Encoding/decoding strings for SQL

  • Thread starter Thread starter Aleko Petkov
  • Start date Start date
A

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
 
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.
 
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
 
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.
Click to expand...

Aleko
 
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).
Click to expand...


Thanks for your help, guys.

Aleko
 
I'am not talking about stored procedures. I'am talking about sql statements
with parameters.
 
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.
 
Back
Top