Concatenation is dropping a single quote

[Paul]

I've got some code that adds a single quote to any ad hoc queries that
appear to look like hacks. For instance, if somebody enters ' OR 1=1 --
then this code adds another single quote the string to neutralize it.
The neutralized string becomes '' OR 1=1 --.

The problem is that when I try to concatenate this string into a SQL
insert statement, the extra single quote is lost. It diskappears! The
hack can then get to the DB.

I tried using Stringbuilder but it has the same effect.

Anybody ever heard of something like this?

~Paul

 

[Marina]

No. You should show some code for a meaningful answer.

 

[Kevin Spencer]

Sure. Doubled single quotes are escaped (literal) single quotes in SQL.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Joe Fallon]

Yes.
That is the way it works.
You are escaping the single quote by doubling it up.
So instead of the 'OR 1=1 -- causing a SQL injection, it just gets added to
your data.
The value in the DB will still have a single quote because it was escaped by
the one you added.

 

[Paul]

Wow, damn, forgot about that! It's behaving.

I was expecting it to store both single quotes to the data store. NOT.

Now it is stored as data instead of an injection, but when I read it I
guess I have to escape the single quote once again to keep it from
injecting at read time.

Thanks.


~Paul