Escaping single quotes through ADO.NET

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mitchell Vincent - 14 Oct 2004 13:15 GMT

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?

Signature

- Mitchell Vincent
- kBilling - An easy and affordable billing solution
- http://www.k-billing.com

Reply to this Message

Bob - 14 Oct 2004 13:57 GMT

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

> 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?

Reply to this Message

Mitchell Vincent - 14 Oct 2004 14:02 GMT

> You can make an escaped single quote with two single quotes in a row, i.e.:
>
[quoted text clipped - 15 lines]
> 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!

Signature

- Mitchell Vincent
- kBilling - An easy and affordable billing solution
- http://www.k-billing.com

Reply to this Message

Bob - 14 Oct 2004 15:31 GMT

> 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
[quoted text clipped - 3 lines]
>
> 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

Reply to this Message

No Sheds - 17 Oct 2004 23:42 GMT

>> 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

Signature

No Sheds

Reply to this Message

Escaping single quotes through ADO.NET

Free Magazines