By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,152 Members | 2,039 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,152 IT Pros & Developers. It's quick & easy.

Protecting SQL injection attacks (text input functino)

P: n/a
I'm learning a bit about the SWL injection issues and want to write a shared
class that I can call from anywhere in my project to 'sanitize' any incoming
text from textfields before sending to the DB.

Is it enough to simply escape single quotes as two single quotes? Ie,
replace ' with ''? Or should I also be checking for things like brackets,
parenthesis and SQL command words (INSERT, UPDATE, DELETE, etc.)?

And...maybe a dumb question, but why doesn't SQL check for these things
automatically?

-Darrel
Nov 18 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
The best way to protect from SQL injection attacks is to utilize
parameterized queries or stored procedures. Sanitizing is fine but I've
always treated it as I do encryption... assume I'm not qualified and
that I'm better off using the existing means that were built by the
"insiders" that know the details (i.e. all the character sequences that
could cause problems).

As for why SQL doesn't filter it out, it is because you are allowed to
submit batches of sql statements for execution. This means that the SQL
server can't tell the difference between you submitting two sql
statements or you submitting one statement that has been hijacked to
hold a second statement of your user's choosing. They provide a means
for mitigating the potential problems this may cause, it is up to us to
take advantage of it.

Have A Better One!

John M Deal, MCP
Necessity Software

Darrel wrote:
I'm learning a bit about the SWL injection issues and want to write a shared
class that I can call from anywhere in my project to 'sanitize' any incoming
text from textfields before sending to the DB.

Is it enough to simply escape single quotes as two single quotes? Ie,
replace ' with ''? Or should I also be checking for things like brackets,
parenthesis and SQL command words (INSERT, UPDATE, DELETE, etc.)?

And...maybe a dumb question, but why doesn't SQL check for these things
automatically?

-Darrel

Nov 18 '05 #2

P: n/a
Darrel,

You are safe if you use the values from the textboxes as query parameters.
In that case you don't need any checking.

SQL can't check for an attack since the whole trick is to send to the server
sql statements with valid syntax.

Eliyahu

"Darrel" <no*****@nospam.com> wrote in message
news:u2**************@TK2MSFTNGP14.phx.gbl...
I'm learning a bit about the SWL injection issues and want to write a shared class that I can call from anywhere in my project to 'sanitize' any incoming text from textfields before sending to the DB.

Is it enough to simply escape single quotes as two single quotes? Ie,
replace ' with ''? Or should I also be checking for things like brackets,
parenthesis and SQL command words (INSERT, UPDATE, DELETE, etc.)?

And...maybe a dumb question, but why doesn't SQL check for these things
automatically?

-Darrel

Nov 18 '05 #3

P: n/a
Escaping quotes is one measure. You should also practice using stored
procedues or parameterized queries if stored procs is not an option (e.g MS
Access). Also, create a sqlcommand or oledbcommand object and specify the
commandtype property to = commandtype.text
Dim cmd as new SqlCommand
cmd.CommandType = CommandType.StoredProcedure
or
cmd.CommandType = CommandType.Text

If you use the Text command type, then you are telling the Database to
process all command as plain text and not as actual sql commands. So, the
single quote should not be a factor it will just be treated like a regular
string.
Hope this helps

"Darrel" wrote:
I'm learning a bit about the SWL injection issues and want to write a shared
class that I can call from anywhere in my project to 'sanitize' any incoming
text from textfields before sending to the DB.

Is it enough to simply escape single quotes as two single quotes? Ie,
replace ' with ''? Or should I also be checking for things like brackets,
parenthesis and SQL command words (INSERT, UPDATE, DELETE, etc.)?

And...maybe a dumb question, but why doesn't SQL check for these things
automatically?

-Darrel

Nov 18 '05 #4

P: n/a
> You are safe if you use the values from the textboxes as query parameters.
In that case you don't need any checking.


Can you explain that? I'm not really sure what a 'query parameter' is.
Right now I have these textboxes:

[firstName]
[lastName]

And then a SQL statement like this:

"INSERT INTO tablename (firstName, lastName) VALUES ('" & firstName.text &
"', '" & lastName.text & "')"

The problem is that any name with an apostrophe, breaks the syntax, so I
need to escape anyways. Is there anything else I need to do, or are these
simply innocuous parameters?

-Darrel
Nov 18 '05 #5

P: n/a
> If you use the Text command type, then you are telling the Database to
process all command as plain text and not as actual sql commands. So, the
single quote should not be a factor it will just be treated like a regular
string.


Ah! That makes sense. Thanks!

-Darrel
Nov 18 '05 #6

P: n/a
You should use ADO.NET parameter objects. They will protect you against SQL
Injection Attacks.

Here's more info:
http://msdn.microsoft.com/library/de...classtopic.asp
http://msdn.microsoft.com/library/de...isualbasic.asp

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
"Darrel" <no*****@nospam.com> wrote in message
news:u2**************@TK2MSFTNGP14.phx.gbl...
I'm learning a bit about the SWL injection issues and want to write a
shared class that I can call from anywhere in my project to 'sanitize' any
incoming text from textfields before sending to the DB.

Is it enough to simply escape single quotes as two single quotes? Ie,
replace ' with ''? Or should I also be checking for things like brackets,
parenthesis and SQL command words (INSERT, UPDATE, DELETE, etc.)?

And...maybe a dumb question, but why doesn't SQL check for these things
automatically?

-Darrel

Nov 18 '05 #7

P: n/a
this is a perfect example of code that allows sql injection.

just type in the lastname textbox

a') delete tablename select ('a
use sqlcomman and parameters

cmd.CommandText = "INSERT INTO tablename (firstName, lastName) VALUES
('@firstname','@lastname')";
cmd.Parameters.Add("@firstname",SqlDbType.VarChar) .Value = firstName.Text;
cmd.Parameters.Add("@lastname",SqlDbType.VarChar). Value = lastName.Text;

-- bruce (sqlwork.com)

"Darrel" <no*****@nospam.com> wrote in message
news:eP**************@TK2MSFTNGP15.phx.gbl...
| > You are safe if you use the values from the textboxes as query
parameters.
| > In that case you don't need any checking.
|
| Can you explain that? I'm not really sure what a 'query parameter' is.
| Right now I have these textboxes:
|
| [firstName]
| [lastName]
|
| And then a SQL statement like this:
|
| "INSERT INTO tablename (firstName, lastName) VALUES ('" & firstName.text &
| "', '" & lastName.text & "')"
|
| The problem is that any name with an apostrophe, breaks the syntax, so I
| need to escape anyways. Is there anything else I need to do, or are these
| simply innocuous parameters?
|
| -Darrel
|
|
Nov 18 '05 #8

P: n/a
> this is a perfect example of code that allows sql injection.
just type in the lastname textbox
a') delete tablename select ('a
would escaping the single quotes as double remedy that?
use sqlcomman and parameters

cmd.CommandText = "INSERT INTO tablename (firstName, lastName) VALUES
('@firstname','@lastname')";
cmd.Parameters.Add("@firstname",SqlDbType.VarChar) .Value = firstName.Text;
cmd.Parameters.Add("@lastname",SqlDbType.VarChar). Value = lastName.Text;


Ah...so, what does that do, exactly? Does it simply send the same text but
as a non-executable command? Is this different than Tamp's suggestion of
setting the entire command as text?

-Darrel
Nov 18 '05 #9

P: n/a
Dude, it's been explained to you more than once that you need to use ADO.NET
parameter objects.
They are the best practice and will prevent all forms of SQL injection
attacks.
Don't escape anything. That's not a good solution. Parameter objects are
the solution.

Here's more info:
http://msdn.microsoft.com/library/de...classtopic.asp
http://msdn.microsoft.com/library/de...isualbasic.asp

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
"Darrel" <no*****@nospam.com> wrote in message
news:uk**************@TK2MSFTNGP15.phx.gbl...
this is a perfect example of code that allows sql injection.
just type in the lastname textbox
a') delete tablename select ('a


would escaping the single quotes as double remedy that?
use sqlcomman and parameters

cmd.CommandText = "INSERT INTO tablename (firstName, lastName) VALUES
('@firstname','@lastname')";
cmd.Parameters.Add("@firstname",SqlDbType.VarChar) .Value =
firstName.Text;
cmd.Parameters.Add("@lastname",SqlDbType.VarChar). Value = lastName.Text;


Ah...so, what does that do, exactly? Does it simply send the same text but
as a non-executable command? Is this different than Tamp's suggestion of
setting the entire command as text?

-Darrel

Nov 18 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.