Escaping single quotes and DBI

ezekiel has asked for the wisdom of the Perl Monks concerning the following question:

I have a CGI script attempting to store information obtained by a form in a MySQL database. The statement looks something like this:

# $comment contains the user form input

my $sql = "insert into my_table " .
          "(COMMENT) " .
          "values ('$comment')";

my $sth = $dbh->prepare($sql);
my $rc = $sth->execute();
[download]

This works fine when $comment is a bunch of text (e.g. "my comment"), but crashes when $comment contains text including a single quote (e.g. "Ezekiel's comment"). I can parse $comment so it contains "Ezekiel''s comment" i.e. escape the single quote with another single quote, but I am wondering if there is a better way to do this as:

it will be a pain in the neck to parse every text field the application uses in order to escape single quotes
I am sure to find myself coming back later parsing the text fields for all sorts of other special characters

Can someone point me in the direction of tools to check incoming CGI parameters and prepare the contents for insertion into a database?

Thanks

Comment on Escaping single quotes and DBI Download Code

Replies are listed 'Best First'.
Re: Escaping single quotes and DBI by maverick (Curate) on Sep 06, 2001 at 07:44 UTC
Use the 'place holder' variation. It's well described in the docs for DBI. `my $sql = "insert into my_table " . "(COMMENT) " . "values (?)"; my $sth = $dbh->prepare($sql); my $rc = $sth->execute($comment);` [download] /\/\averick perl -l -e "eval pack('h*','072796e6470272f2c5f2c5166756279636b672');"	[reply] [d/l]
Re: Escaping single quotes and DBI by lachoy (Parson) on Sep 06, 2001 at 07:42 UTC
Yup: read the DBI docs. Your problem is not specific to CGI -- it's just text, after all. In particular, go down to the `quote()` method and, if you're feeling industrious, read up on placeholders. Additionally, a Super Search right here on Perlmonks using 'DBI' and 'quote' turns up a boatload of links. Good luck Chris M-x auto-bs-mode	[reply] [d/l]
Re: Escaping single quotes and DBI by pmas (Hermit) on Sep 06, 2001 at 09:42 UTC
You are right, it will be pain and wrong. Please check the links: Tricks with DBI - you need placeholders, follow the link; Learning CGI & DBI well. Start from there and come again soon asking more questions. Good luck! pmas To make errors is human. But to make million errors per second, you need a computer.	[reply]
Re: Escaping single quotes and DBI by ezekiel (Friar) on Sep 06, 2001 at 11:37 UTC
Thanks for the pointers, I solved the problem by familiarizing myself with the `quote` method. However, I'd like to explore the placeholder stuff. On seeing the responses I asked myself the same question: why am I not using placeholders? To explain I have to expand the example slightly. Imagine it reads: `# $comment is a text comment, $id is a integer my $sql = "insert into my_table ". "(ID, COMMENT) " . "values (?, ?)"; my $sth = $dbh->prepare($sql); my $rc = $sth->execute($id, $comment);` [download] This works fine with MySQL as the database, but I am also using the same code with DB2. It seems DBI quotes the $id integer. DB2 therefore thinks it is a string and refuses to insert it because the column takes integers. I'll keep working my way through the DBI docs, but does anyone know why this should be the case? or a way around it? Thanks again	[reply] [d/l] [select]
Re: Escaping single quotes and DBI by flocto (Pilgrim) on Sep 06, 2001 at 14:30 UTC
MySQL has a "different" (not to say weird) way to quote quotes (hm, sounds funny, doesn;t it?). Anyways, it workslike this: `'The guy''s house' is "The guy's house" in perl (well, you could write + 'The guy\'s house', too..) "He said ""Hello""" is "He said \"Hello\"" in perl (or, 'He said "Hell +o"')` [download] Knowing that you can use a very simple regexp to quote your comment: `#i assume here that you define $comment somewhere else.. $comment =~ s/"/""/g; # if you use double-quotes # OR: $comment =~ s/'/''/g; # is you use single-quotes` [download] Hope this was of any help for you :)	[reply] [d/l] [select]