Re: [Sqlrelay-discussion] FreeTDS bind incorrectly escaping strings

[David M. <dav...@fi...>]

Hey Michael,

Wow, I had no idea that MS SQL Server didn't use backslashes!

A quick fix, assuming that you're not using SQL Relay with any db other
then SQL Server, would be to edit src/connection/sqlrcursor/fakebinds.C
around line 119 and change:

			if (ch=='\'' || ch=='\\') {
				buffer->append('\\');

to:
			if (ch=='\'') {
				buffer->append('\'');

Then rebuild/reinstall.

I'll fix it for real in the next release.

David Muse
dav...@fi...


On Thu, 2008-03-27 at 00:25 +1300, Michael Adams wrote:
> Hi there,
> 
> Ran into an interesting problem today... it appears that bound input 
> variables are escaped incorrectly when running against SQL Server. 
> Basically it is escaping quotes with a backslash (unix style), not 
> double quotes (MS style).
> 
> E.g. the following PHP code...:
>         $sql = "SELECT * FROM tblPatron WHERE FirstName = @first_name";
>         $args['first_name'] = "O'Rourke";
>        
>         $q = sqlrcon_alloc("localhost", $port, $socket, $username, 
> $password, 1, 1);
>         $c = sqlrcur_alloc($q);
>        
>         sqlrcur_prepareQuery($c, $sql);
>         foreach($args as $a => $b)
>             sqlrcur_inputbind($c, $a, $b);
>         $o = sqlrcur_executequery($c);
>         if ($o != 1) throw new Exception("Error with query: " . 
> sqlrcur_errorMessage($c));
>         for ($i=0; $i<sqlrcur_rowcount($c); $i++)
>             $result[] = (object) sqlrcur_getrowassoc($c, $i);
>        
>         sqlrcur_free($c);
>         sqlrcon_free($q);
> 
> 
> This results in the following error:
>     Error with query: Server message:
>     severity(0)
>     number(102)
>     state(1)
>     line(1)
>     Server Name:
>     HAMLET\SQLEXPRESS
>     Procedure Name:
>    
>     Error: Incorrect syntax near 'Rourke'.
> So the SQL passed to the DB would be: SELECT * FROM tblPatron WHERE 
> FirstName = 'O\'Rourke'; SQL server doesn't treat the backslash 
> specially so strings ends before the R.
> It should be: SELECT * FROM tblPatron WHERE FirstName = 'O''Rourke';
> 
> If I set $args['first_name'] = "O''Rourke" (two single quotes) then I 
> get: Error: Incorrect syntax near '\'.
> So the SQL passed to the DB would be: SELECT * FROM tblPatron WHERE 
> FirstName = 'O\'\'Rourke'; so this time the statement ends at the second \
> It should be: SELECT * FROM tblPatron WHERE FirstName = 'O''''Rourke';
> 
> Am I doing something wrong? The only relevant info I could find is that 
> under FreeTDS connections the binds are faked, but nothing how strings 
> are escaped.
> 
> Thanks in advance, SQLR has made a massive (~ 5x) improvement in 
> database access times here.
> --Michael Adams
> 
> -------------------------------------------------------------------------
> Check out the new SourceForge.net Marketplace.
> It's the best place to buy or sell services for
> just about anything Open Source.
> http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 
> 
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com



__________________________________________________
D O T E A S Y - "Join the web hosting revolution!"
             http://www.doteasy.com