Encoding/decoding strings for SQL

[Aleko Petkov]

Hi,

This must be a common topic, but I couldn't find anything on Google. Are
there built-in functions in .NET to encode and decode strings so they can
safely be passed to SQL Server?

For example, if I want to pass something like "Mike's Place" as a query
parameter, I would need to escape the single quote so it doesn't trip up SQL
Server. Right now, I'm rolling my own functions using regular expressions,
but I have a feeling this has already been done.

Thanks,

Aleko

 

[Marina]

Well, normally it is just a matter of replacing any single quote, with 2
single quotes. There is really not anything else to it.

The recommended way however, is to use parameters in your query. When you
set the value of a paramter object, it takes care of all that for you.

 

[Dumitru Sbenghe]

If you use sql parameters (@ and CreateParameter) you don't need to replace
the single quote with double quote. You will need this only if you manually
build the sql string by concatenation.

This is one of the reasons that sql statements with parameters are better
than raw sql strings (They also save you from sql injection).

I hope I understood your question correctly.

Dumitru

 

[Aleko Petkov]

What about other special characters, like ~@#%^? The presence of any of
these in the querystring can upset the db engine, and result in an
exception. If text is to be entered into the db via a querystring then any
special characters will need to be encoded.

Well, normally it is just a matter of replacing any single quote, with 2
single quotes. There is really not anything else to it.

Aleko

 

[Aleko Petkov]

That's true, stored procs are the premier way of doing database access.
Sometimes it's tempting to write a quick query, rather than a stored
procedure, but now that I think about it, it's just not worth doing the
extra work to encourage a bad habit.

If you use sql parameters (@ and CreateParameter) you don't need to
replace
the single quote with double quote. You will need this only if you
manually
build the sql string by concatenation.

This is one of the reasons that sql statements with parameters are better
than raw sql strings (They also save you from sql injection).

Thanks for your help, guys.

Aleko

 

[Dumitru Sbenghe]

I'am not talking about stored procedures. I'am talking about sql statements
with parameters.

 

[Marina]

I don't believe those cause problems. At least not as far as I know.

But like i said, you are better off using parameterized queries, and not
having to worry about it all.