Is there a built in command to encode SQL strings?

[Christian Blackburn]

Hi Gang,

When encoding HTML strings it'll convert things like " --> &rsquo and
the like using Server.HTMLEncode(). However, is there a command to
make sure strings don't contain valid SQL commands? Like I wouldn't
want a string to contain
"; Drop TableXYX;" or something along those lines.

Thanks,
Christian Blackburn

 

[Guest]

Christian,

I don't know of a way to predetermine if a string contains valid SQL.

Are you wanting to do that so you can concatenate strings into an SQL
statement? If so, that is what parameters are for.

Use parameters to prevent sql injection attacks.

Kerry Moorman

 

[Cor Ligthert [MVP]]

Christian,

Can you explain this more, because in normal situations those commands don't
go over the line by ASPNet. Not in build as well not in scripting mode.

Cor

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

SQL commands inside strings are harmless, as long as you encode the
strings correctly.

For an example, this query is safe:

"insert into TableXYX (Description) values ('; Drop TableXYX;')"

How strings should be encoded differ from database to database.

Access: Replace "'" with "''".
MS SQL: Replace "'" with "''".
MySQL: Replace "\" with "\\", then "'" with "\'".

As Kerry pointed out, this can be handled by using parameters. Then you
don't need to worry about what characters it is that needs to be encoded.

 

[Jim Wooley]

In addition, you should always check the values your users are entering to
make sure they don't include invalid characters. If you check your input
to make sure it doesn't include the single tick (') and semicolon, you don't
need to worry about that. In many ways, it is better to specify only the
valid characters.

For instance, in my applications, I have a method that checks for valid characters
on a per-property basis using regex. If I have a field that only allows characters,
digits, comma and space I can do something like:

If Not RegEx.IsMatch(value, "[^A-Za-z\d-, ]") then
Throw New ArgumentException
End If

In addition to filtering for the correct values, USE PARAMETERIZED QUERIES
or stored procedures.

Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx

 

[Jim Wooley]

Update: That should read:

If RegEx.IsMatch(value, "[^A-Za-z\d-, ]") then

The Not is included in the regex string (^)

Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx

In addition, you should always check the values your users are
entering to make sure they don't include invalid characters. If you
check your input to make sure it doesn't include the single tick (')
and semicolon, you don't need to worry about that. In many ways, it is
better to specify only the valid characters.

For instance, in my applications, I have a method that checks for
valid characters on a per-property basis using regex. If I have a
field that only allows characters, digits, comma and space I can do
something like:

If Not RegEx.IsMatch(value, "[^A-Za-z\d-, ]") then
Throw New ArgumentException
End If
In addition to filtering for the correct values, USE PARAMETERIZED
QUERIES or stored procedures.

Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx

SQL commands inside strings are harmless, as long as you encode the
strings correctly.

For an example, this query is safe:

"insert into TableXYX (Description) values ('; Drop TableXYX;')"

How strings should be encoded differ from database to database.

Access: Replace "'" with "''".
MS SQL: Replace "'" with "''".
MySQL: Replace "\" with "\\", then "'" with "\'".
As Kerry pointed out, this can be handled by using parameters. Then
you don't need to worry about what characters it is that needs to be
encoded.

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

In addition, you should always check the values your users are entering
to make sure they don't include invalid characters. If you check your
input to make sure it doesn't include the single tick (') and semicolon,
you don't need to worry about that.

If correctly encoded, strings may very well contain apostrophes (').
Semicolon has no special meaning inside strings, so that is no concern.

Actually, as long as the strings are encoded correctly, there are no
characters that causes problems for the database. It's true that all
input should be validated, but for strings it's mostly a matter of
keeping the information sane rather than protecting the database.

In many ways, it is better to
specify only the valid characters.
For instance, in my applications, I have a method that checks for valid
characters on a per-property basis using regex. If I have a field that
only allows characters, digits, comma and space I can do something like:

If Not RegEx.IsMatch(value, "[^A-Za-z\d-, ]") then

Hmm... What is the hyphen doing between \d and the comma?

Throw New ArgumentException
End If

In addition to filtering for the correct values, USE PARAMETERIZED
QUERIES or stored procedures.

Or rather, always use parameteters, with or without stored procedures.