Home: Perl Programming Help: Advanced:
Single quotes in sql-queries


Oct 16, 2000, 1:10 AM

Views: 2578
Single quotes in sql-queries

I use the following Perl-code to parse a query to my sql-database:
$sth = $dbh->prepare($sqlstr);

$sqlstr will contain single quotes, but not all of them are allowed.

Example where single quotes are OK:
$sqlstr = "INSERT INTO EXAMPLE (id) VALUES ('10')"

Example where single quotes are not OK:
$sqlstr = "INSERT INTO EXAMPLE (name) VALUES ('john's book')"

So, only the quote after john should be replaced by \'.

How can I do that? I mean how can I replace only the wrong quotes?

The Netherlands



Oct 16, 2000, 6:52 AM

Views: 2578
Re: Single quotes in sql-queries

Here is a short example of what you could do using the quote subroutine. This subroutine will quote what you pass it, as well as escaping all other quotes in the string.

$id = 8847;
$id = $dbh->quote($id);
$title = q{Welcome to "John's House"};
$title = $dbh->quote($title);
$dbh->do("insert BookTitle (id, title) values ($id, $title)");

It should be pretty straight forward. If you need more help look in the DBI docs or let me know.


User / Moderator

Oct 16, 2000, 8:53 AM

Views: 2578
Re: Single quotes in sql-queries

You can also use placeholders which just looks neater and lets DBI work out quoting issues itself...

<BLOCKQUOTE><font size="1" face="Arial,Helvetica,sans serif">code:</font><HR>

$sth = $dbh->prepare( "INSERT INTO example ( id, name ) VALUES ( ?, ? )" );
$sth->execute( "10", "john's book" );</pre><HR></BLOCKQUOTE>