[Mono-list] escaping a string for sql

[James Grant]

Here I am replying to my own post - with the solution that I ended up coming 
up with.

Since there are three 'parts' to the code
1) the c# code
2) the stored procedure call
3) the query inside the stored procedure

I found that the string in the code needed to be escaped three times in order 
to end up with an escaped string for the query.

eg:

str = "public.\"sp_function\"('" + LastName.Replace("'","\\\\\\'") + "' )");

so the parameter ends up having a single '  replaced with  \\\\\\' in the 
code, which is unescaped as \\\' for the sp_function() parameter, then 
unscaped again as \' for the query inside the stored procedure.

perhaps using the @ infront of the string would make things easier to 
understand (require less escaping) but its working now at least.

Thanks for all your help/suggestions.

Cheers,
  James

On Monday 07 March 2005 8:31 am, James Grant wrote:
> I know this probably isnt the right place to ask, but I figured someone
> here might  know (and google seems useless in this case) -- how do you
> escape a string in C# for use in an SQL query?  in php/mysql I would do
> mysql_escape_string("string with ' or ` in it")
>
> all i'm doing is a simple SQL SELECT based on the input of a text box, but
> the text box must handle all input (apostrophe's, quotes, etc) -- here's
> what Npgsql is saying when I enter   "apo'strophe" in the textbox.
>
> Npgsql.NpgsqlException:
> syntax error at or near "strophe"
> Severity: ERROR
> Code: 42601
> in <0x00061> Npgsql.NpgsqlConnection:CheckErrors ()
>
> Thanks,
>  James

-- 
James Grant
Lightbox Technologies Inc.
http://www.lightbox.org
613-294-3670