SQL quoting problem

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

I'm having an SQL quoting problem, but not quite of the "normal" caliber.

I'm running ActiveState perl 5.6.0 on Windows 2000. I'm using DBD::ODBC to connect to an SQLServer 200 DB on the same host. The problem comes when I want to call a stored procedure and one of the arguments I want to pass to it contains a single quote. I've tried all the ways I know of to get around the escaping problem:

$dbh->do('exec sp_MyStoredProg @foo=' . $dbh->quote($bar));
[download]

$bar=~s/'/''/g;
$dbh->do("exec sp_MyStoredProg \@foo='$bar'");
[download]

my $sth=$dbh->prepare('exec sp_MyStoredProg @foo=?');
$sth->execute($bar);
[download]

But whatever I do it doesn't seem to like the escaping of any single quotes in $bar:

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL 
+Server]Line 1: Incorrect syntax near 's'. (SQL-37000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark
+ before the character string '.... (SQL-37000)(DBD: st_execute/SQLExe
+cute err=-1) at testdiff.pl line 113.
[download]

I believe this to be an MS/SQLServer problem, not a DBD::ODBC problem (but I could be wrong). I'm hopeing that someone here will have a good workaround that can get me running.

Thanks, Les

Comment on SQL quoting problem Select or Download Code

Replies are listed 'Best First'.
Re: SQL quoting problem by extremely (Priest) on Oct 14, 2000 at 03:43 UTC
What does MyStoredProg do? Does it rehandle that string in such a way that you should double pass the string thru your quoter? ala `$dbh->do('exec sp_MyStoredProg @foo=' . $dbh->quote($dbh->quote($bar)) +);` [download] That is ugly but that MIGHT be it. -- $you = new YOU; honk() if $you->love(perl)	[reply] [d/l]
RE: Re: SQL quoting problem by lhoward (Vicar) on Oct 14, 2000 at 04:46 UTC
Thanks to everyone for all their help. Everyone gets a ++, but extremely gets the gold star! It was his comment about double-quoting that let me to a solution to my problem. Turns out that quoting it twice won't do it, because that doubles the surrounding quotes too. But I got the following to work: `$bar=~s/'/''/g; $dbh->do('exec sp_MyStoredProg @foo=' . $dbh->quote($bar));` [download] which effectively turns something like can't into 'can''''t', which is just what the stored proc needs. Thanks again to everyone!	[reply] [d/l]
Re: SQL quoting problem by chromatic (Archbishop) on Oct 14, 2000 at 03:21 UTC
Here is a random guess, based on the fact that I've never used SQLServer: `$dbh->do(qq{exec qp_MyStoredProg \@foo='} . $dbh->quote($bar) . "'");` Take that as untested and only offered because no one else has given a more definitive answer yet.	[reply] [d/l]
(Ovid) Re: SQL quoting problem by Ovid (Cardinal) on Oct 14, 2000 at 03:31 UTC
Also a guess: if you `use DBI;` along with DBD::ODBC, you can do this: `DBI->trace(2, $somefile);` [download] The number can range from 0 to 5, with 0 being no information to 5 being too much (I find levels two and three to be useful). This command opens `$somefile` in append mode and writes out tons of trace information, including the full text of the SQL, which should let you see the exact error. I use this all the time when I have complex issues. Cheers, Ovid Join the Perlmonks Setiathome Group or just go the the link and check out our stats.	[reply] [d/l]
Re: SQL quoting problem by blogan (Monk) on Oct 14, 2000 at 04:09 UTC
I've had a problem similar to this. Usually I ended up doing something like: `$statement = something.... $dbh->do($statement);` [download] And that's worked for me, but I haven't been doing anything with single quotes.	[reply] [d/l]
Re: SQL quoting problem by wardk (Deacon) on Oct 16, 2000 at 17:34 UTC
I've used this module quite a bit. I notice you aren't escaping your single quotes. I have used the following syntax without problems. `my $Sql = "select x, y, z from tab where this = \'that\'";` [download]	[reply] [d/l]
Re: SQL quoting problem by cadfael (Friar) on Oct 16, 2000 at 20:39 UTC
For what it is worth, Sybase (related to SQL Server) will tolerate both single and double quotes in sql queries. You need to be consistent, though. I am using Sybase ASE 12.0 and DBD::Sybase to allow users to query a full-text server that joins with another database. I put my queries into variables and escape any embedded quotes. This permits me to employ nested quotes in certain situations: `$query = "select col_a, col_b, col_c from tablea, tableb where tablea..pkey = tableb..fkey and tablea..column_x = '$fts'";` [download] The $fts string must be enclosed in single quotes, and may contain literals, which need to be enclosed in double quotes: `$fts = "(seedling \<or\> kernel \<or\> embryo) \<and\> \"viab\" ";` [download] Note that the only time I actually need to escape the quotes are when a double quote is employed within a string variable. The final form of the query passed to the Sybase server is: select a,b,c from tablea, tableb where tablea..pkey = tableb..fkey and tablea..column_x = '(kernel <or> embryo) <and> "viab"' ----- "Computeri non cogitant, ergo non sunt"	[reply] [d/l] [select]