|
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
|
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 | [reply]
[d/l]
[select] |
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.
| [reply]
[d/l] |
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 | [reply]
[d/l] |
|
|
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
| [reply] |
Re: Inserting into SQL
by rdfield (Priest) on Feb 13, 2002 at 10:04 UTC
|
Have you checked the output from $dbh->errstr?rdfield | [reply]
[d/l] |
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... | [reply] |
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
| [reply] |
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. | [reply]
[d/l] |
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 | [reply]
[d/l]
[select] |
|
|
