Protecting SQL injection attacks (text input functino)

[Darrel]

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

[John M Deal]

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

[Eliyahu Goldin]

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

[Tampa .NET Koder]

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

[Darrel]

> 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

[Darrel]

> 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

[Steve C. Orr [MVP, MCSD]]

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

[bruce barker]

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
|
|

[Darrel]

> 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

[Steve C. Orr [MVP, MCSD]]

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