Preventing ALL text SQL Injection by removing single-quotes ?

I need to ask the user for some string (not INT) input... to
build a T-SQL "find" query in SQL Server 2000.
I do *NOT* need the user to ever search for "'"... so I'm removing the
single-quote character entirely.

> s = Replace(s, "'", "")
Can someone give a working example where SQL INJECTION would still be
possible?Hi Susan
You may want to read:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.sommarskog.se/dynamic_sql.html#Security2
John
"Susan S via droptable.com" <forum@.droptable.com> wrote in message
news:50B826701CBE0@.droptable.com...
>I need to ask the user for some string (not INT) input... to
> build a T-SQL "find" query in SQL Server 2000.
> I do *NOT* need the user to ever search for "'"... so I'm removing the
> single-quote character entirely.
>
> Can someone give a working example where SQL INJECTION would still be
> possible?|||I can't find a single line of that article that *SHOWS* how a
string, fully stripped of all single-quotes... can be used for
Injection.
I'm not saying that it can't.
I just need someone to post the actual code.
Ever wonder why no one has the code?
Message posted via http://www.droptable.com|||Hi
You are assuming a certain business rule is applied which is probably
quite rare. You may be able to provide dropdowns if your values are so
specific and remove the need to type in anything!!
The parameterised query option is generic and can cater for all
sutuations, it should also be fast.
John|||John... that wasn't the question at all.
I *DO* have a need to search for text without containing any single-quotes.
(I couldn't possible have a dropdown box with 1000 different searches in it.
)
Why not simple say "I don't know" instead of answering a totally different
question?
John Bell wrote:
>Hi
>You are assuming a certain business rule is applied which is probably
>quite rare. You may be able to provide dropdowns if your values are so
>specific and remove the need to type in anything!!
>The parameterised query option is generic and can cater for all
>sutuations, it should also be fast.
>John
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...curity/200507/1|||"Susan S via droptable.com" <forum@.droptable.com> wrote in message
news:510817BAE9A30@.droptable.com...
> John... that wasn't the question at all.
> I *DO* have a need to search for text without containing any
> single-quotes.
> (I couldn't possible have a dropdown box with 1000 different searches in
> it.)
> Why not simple say "I don't know" instead of answering a totally different
> question?
You don't provide us with an example of your code that builds dynamic SQL so
we can only guess as to what might or might not work. Have you considered
char(39) being mixed in somehow?
But the larger question is, what is your aversion to parameterizing your
queries? If you were truly concerned about SQL injection, that's the route
you would go. Will you feel safe if nobody here can come up with an
example? Will that mean that it can't be done? You'd learn more hanging
out in hacker's circles, if that is your approach..
So, sorry if it offends you, and I'll admit that "I don't know" of a
specific example, but the real answer to your rhetorical question is:
parameterize, then you won't need to worry about it, there are no reasons it
can't be done, only excuses.
Good Luck,
Mark
[vbcol=seagreen]
> John Bell wrote:|||Well I started out the other day writing down a list from the sql server
perspective, but they all seem to start with the programmer making a
blunder. There are so many ways you can facilitate your site getting hacked
that it is really quite absurd. This is where a lot of the challenge exists.
If ever in the design of the system someone has said "It will never do such
and such" so you haven't coded defensively for the day you will have say
multiple sites on the one server (as an EG) then you have introduced time
bombs.
The most important thing about security is like so many things - expend your
effort where it will have most benefit.
I suggest you check your program code and ensure that no strings can
overflow buffers, that you use parameterised queries, that you check every
string not just for single quotes but also script and other HTML tags, for
code that can be evaluated to quotes, script, or html, that wherever
possible you remove as much need for free text entry as you can, do not
reflect errors verbatum back to the user - IE avoid replay attacks - do not
store data ever without sanitising it, do not show data with it being
santised (again), make everything as type safe as possible, normalise your
database properly, use maximum error and warning reporting when compiling,
use proper error handling, test the system thoroughly, try to break it, do
not accept warnings during compiles, and so on.
Add to that, use a DMZ, secure your web server, secure your database server,
enforce strong security... strong passwords,... I think you get the idea.
Now, look at your original question and ask yourself "was it responsible"?
Or do you think you can now check off the single quote character in
isolation? It is never a done task.
I suggest you re-read John's answer and references as a starter.
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:uX1WrSUhFHA.3256@.TK2MSFTNGP12.phx.gbl...
> "Susan S via droptable.com" <forum@.droptable.com> wrote in message
> news:510817BAE9A30@.droptable.com...
> You don't provide us with an example of your code that builds dynamic SQL
> so we can only guess as to what might or might not work. Have you
> considered char(39) being mixed in somehow?
> But the larger question is, what is your aversion to parameterizing your
> queries? If you were truly concerned about SQL injection, that's the
> route you would go. Will you feel safe if nobody here can come up with an
> example? Will that mean that it can't be done? You'd learn more hanging
> out in hacker's circles, if that is your approach..
> So, sorry if it offends you, and I'll admit that "I don't know" of a
> specific example, but the real answer to your rhetorical question is:
> parameterize, then you won't need to worry about it, there are no reasons
> it can't be done, only excuses.
>
> Good Luck,
> Mark
>
>
>
>|||Oh, of course, the biggest sin of them all - as Mark points out. Never use
dynamic SQL. You never need to for a correctly designed database.
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:uX1WrSUhFHA.3256@.TK2MSFTNGP12.phx.gbl...
> "Susan S via droptable.com" <forum@.droptable.com> wrote in message
> news:510817BAE9A30@.droptable.com...
> You don't provide us with an example of your code that builds dynamic SQL
> so we can only guess as to what might or might not work. Have you
> considered char(39) being mixed in somehow?
> But the larger question is, what is your aversion to parameterizing your
> queries? If you were truly concerned about SQL injection, that's the
> route you would go. Will you feel safe if nobody here can come up with an
> example? Will that mean that it can't be done? You'd learn more hanging
> out in hacker's circles, if that is your approach..
> So, sorry if it offends you, and I'll admit that "I don't know" of a
> specific example, but the real answer to your rhetorical question is:
> parameterize, then you won't need to worry about it, there are no reasons
> it can't be done, only excuses.
>
> Good Luck,
> Mark
>
>
>
>|||Wow.. it was just *ONE* simple request:
Post some *ACTUAL* code... that can cause SQL injection... where *ALL* singl
e
quote
characters have been removed from the string input by the user.
Seems like everyone is talking about eveything *OTHER* than that 1 simple
request.:

> Why not use another method instead?
> Why not fully secure your servers instead?
> Why not post your code?
> What are you using this for?
> You might needs users to enter single quotes some day.
> Why not write/use long parameterized methods instead?
> What if it's an INT, not a VarChar field?
> Why don't you go back and change 1000s of lines of code that you already w
rote?
> Let's talk about some totally different problems, instead of this one.
Sheezesss. That wasn't the question here. Here it is again:
s = GetUsersString() ' Get the input
s = Replace(s, "'", "") ' Remove all single-quotes
sql = "SELECT * FROM MyTable WHERE MyField='" & s & "'" ' Execute this
The Question:
In *ONE* line... without *ANY* explaination... what text would the user ente
r
as a value for "s",
that would break the above code sample?
So simple. No one can answer that? Or prove it can't be done?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...curity/200507/1|||"Susan S via droptable.com" <forum@.droptable.com> wrote in message
news:5131626AFB930@.droptable.com...
> Wow.. it was just *ONE* simple request:
Considering all the good and relevant (despite your views) advice you've
received, it's obvious that you just don't get it; so go ahead and feel as
though you've written safe, secure code. Clearly, your mind is made up. I
think you've insulted the NG with your narrow-minded replies quite enough
for one thread.
What you refuse to accept is that it doesn't matter whether or not anyone in
this or any NG can provide you with an example. It simply doesn't matter.
It proves/disproves nothing. It does not mean it can't be done. You want
someone to prove for you that it *can* be done, otherwise you'll assume it
can't? That is a reckless approach at best.
So you choose to believe what you want to believe -- ok, fine. You chose to
ignore widely accepted best practices and now you want to justify that
choice -- that's all up to you. But don't expect the NG to validate those
choices, because -- and you know this as well as we do -- there is a body of
knowledge that says your chosen methodology is not the most secure, PERIOD,
bottom line, end of story.
And given today's Internet climate, why anyone would choose to do something
any way other than the most secure defies all reason. That's what we're on
about. That's why we can't get behind your over-simplification of the
issues.
And now I suppose you'll post another rude, frustrated, closed-minded reply
because I didn't stick to a one line answer... whatever... best of luck to
you too.
-Mark