Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Inserting into SQL

by Anonymous Monk
on Feb 13, 2002 at 10:02 UTC ( #145118=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi PerlMonks

I am doing a feedback form which saves data into mysql, but it seems to fail whenever the feedback form has special chars in it - @:"_<' - etc

Im using an INSERT INTO .... ?, ?, ?
$sth->execute( $var1, $var2 );

Any tips would be appreciated, and the best way to approach saving user input

Thanks

Comment on Inserting into SQL
Re: Inserting into SQL
by rdfield (Priest) on Feb 13, 2002 at 10:04 UTC
    Have you checked the output from $dbh->errstr?

    rdfield

Re: Inserting into SQL
by kwoff (Friar) on Feb 13, 2002 at 10:36 UTC
    You could try doing something like
    $dbh->trace(2); # verbose debugging output # your DBI stuff here $dbh->trace(0);
    and check the log file to see what query is actually being sent.
Re: Inserting into SQL
by snowcrash (Friar) on Feb 13, 2002 at 11:14 UTC
    sounds like a quoting problem to me. when dealing with text or binary data always quote the value like:
    my $sql = "INSERT INTO feedback SET fulltext=" . $dbh->quote($feedback);

    hth,
    snowcrash
      The poster is already using placeholders, which is guarenteed to quote the inputs (either by the DB itself or by DBI if the DB doesn't support it). And placeholders are always more desirable to use than to place the inputs into the SQL statement itself, even if you quote them.

      -----------------------------------------------------
      Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
      "I can see my house from here!"
      It's not what you know, but knowing how to find it if you don't know that's important

Re: Inserting into SQL
by George_Sherston (Vicar) on Feb 13, 2002 at 12:53 UTC
    It wd be useful to see all your code - but on the face of it, it looks like you have three placeholders and only two values to go into those places. Perhaps that's not that problem - but it wd be *a* problem. Having said that, you are going the right way in using placeholders, and used correctly they will eliminate quoting problems - so do persevere, as the truth is certainly out there.

    In general, rdfield is quite right - I'd advise putting or die $dbh->errstr; at the end of each DBI call, so it tells you what went wrong.

    Or you could use this, which works for me in a wide variety of circumstances:
    sub InsertMultipleValues { #--------------------------------------------------------------- # Inserts contents of a hashref into the db table specified #--------------------------------------------------------------- my $dbh = shift; my $table = shift; my $Inserts = shift; my @cols = keys %$Inserts; my @vals = @$Inserts{@cols}; my $cols = join ',', @cols; my $places = '?,' x @vals; chop $places; my $sth = $dbh->prepare("INSERT INTO $table ($cols) VALUES ($place +s)") or die $dbh->errstr; $sth->execute(@vals) or die "$dbh->errstr : $table"; }

    To use it, you'd organise the values you want to insert into a hash, where the keys are the names of the columns they go into. Then you get a database handle, and call the sub with InsertMultipleValues($dbh,'your_table',\%your_hash);

    George Sherston
Re: Inserting into SQL (boo)
by boo_radley (Parson) on Feb 13, 2002 at 14:36 UTC
    This could also be part of the table's design -- for instance, it may have a trigger that allows for only alphanumeric data in a particular column, or a format which disallows some of the characters you tried to enter.

    update : The former's not much of a possibility -- MySQL doesn't have triggers, apparently...

Re: Inserting into SQL
by thraxil (Prior) on Feb 13, 2002 at 14:46 UTC

    be careful that there aren't any null characters (0x00) getting into it. i've seen this be a problem with DBI::Pg before. at some point after quoting, if a null character is in the sql statement, some C library somewhere sees that as the end of the string and dies.

    but, as others have pointed out, some more code would probably make the problem much easier to diagnose.

    anders pearson

Re: Inserting into SQL
by beebware (Pilgrim) on Feb 13, 2002 at 17:13 UTC
    Even though you are using placeholders, it's still a good idea to use a regexp to remove anything you don't want.
    $var1=~s/[^A-z0-9, \.]//g;
    works for me, but a) the little regexp can probably be better written and b) only allows set characters (upper and lower case letters, numbers, commas, spaces and full stops). Just an added layer of security.
Re: Inserting into SQL
by buckaduck (Chaplain) on Feb 14, 2002 at 01:29 UTC
    This is a real long shot, but just in case...

    If all of the following conditions are true:

    • Your program is running in taint mode (perl -T).
    • Your program already "untaints" CGI parameters via a regex pattern.
    • These special characters are failing the regex.

    Then perhaps one of the following conditions is true:

    • Your program voluntarily quits via something like the die command.
    • Your program performs an "unsafe" action with the tainted data, and the program automatically dies.
    • The database connection specifies Taint=>1, and when you try to insert the tainted data the program dies.

    If any of these are true, surely the web server's error log will tell you about it.

    buckaduck

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://145118]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (12)
As of 2014-11-26 11:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (168 votes), past polls