Escaping single quotes through ADO.NET

[Mitchell Vincent]

Is there some built in functionality to escape special characters for
use through ADO? I'm sure I could manually replace the ' with ' (if that
is the right escape sequence), but I'm afriad I just missed the built in
escaping functions.. Did I?

 

[Bob]

You can make an escaped single quote with two single quotes in a row, i.e.:

SELECT 'This is how you ''quote'' something'

---------------------------------
This is how you 'quote' something

(1 row(s) affected)

Linefeed/carriage return is CHAR(10), CHAR(13), and CHAR will give you
anything else you need, too.

Lastly, square brackets can be used to force a literal, for instance if you
had a table name "'test'" (single quote, test, single quote - very bad name
for a table but just in example), you would access it with SELECT * FROM
['test']

HTH,
Bob

 

[Mitchell Vincent]

You can make an escaped single quote with two single quotes in a row, i.e.:

SELECT 'This is how you ''quote'' something'

---------------------------------
This is how you 'quote' something

(1 row(s) affected)

Linefeed/carriage return is CHAR(10), CHAR(13), and CHAR will give you
anything else you need, too.

Lastly, square brackets can be used to force a literal, for instance if you
had a table name "'test'" (single quote, test, single quote - very bad name
for a table but just in example), you would access it with SELECT * FROM
['test']

HTH,
Bob

So there isn't any built in escape routines in ADO.NET (or .NET in
general) for escaping.. All the data coming from the user I'll have to
sData.replace("'","''") before attaching it to the query string or
sending it down to the database - correct? I just want to make sure
there isn't an easier way before I start to use that

I'm impressed with this newsgroup - fast and friendly! Thanks!

 

[Bob]

So there isn't any built in escape routines in ADO.NET (or .NET in
general) for escaping.. All the data coming from the user I'll have to
sData.replace("'","''") before attaching it to the query string or
sending it down to the database - correct? I just want to make sure
there isn't an easier way before I start to use that

I'm impressed with this newsgroup - fast and friendly! Thanks!

Glad to help.

I don't know of any built-in escape routines, but I could be wrong.

Replacing single quotes is just the beginning. Read this to be brought up to
speed about handling raw user data:

* Stop SQL Injection Attacks Before They Stop You

http://go.microsoft.com/?linkid=949336



It's also fairly important to read on its own merits.



Good luck!



Bob

 

[No Sheds]

So there isn't any built in escape routines in ADO.NET

[]
All the data coming from the user I'll have to

sData.replace("'","''")

[]

I don't know of any built-in escape routines, but I could be wrong.

Replacing single quotes is just the beginning. Read this to be brought up to
speed about handling raw user data: []
http://go.microsoft.com/?linkid=949336

Just had a quick skim of that article - it's interesting! There seem to
be loads of potential problems with building up SQL statements "on the
fly".

Rather than build SQL on the fly, I prefer to use a stored procedure (in
SQL Server) or a Query (in Access). I prefer these because
a) I think they're faster
b) They're more secure and
c) You can pass things like dates or names like O'Brien to them without
having to do stuff like replace ' with ''. You do this using Parameters
to your Query/Stored Procedure.

Sorry, I don't have time to post an example here, but look up
parameterized queries or some such and you should find enough help.

If you really must build SQL "on the fly", you can even write it to
include parameters.

HTH