Is there a built in command to encode SQL strings?

9 Jun 2006 10:15 PM

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

9 Jun 2006 11:38 PM

Kerry Moorman

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

Show quoteHide quote

"Christian Blackburn" wrote:

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

10 Jun 2006 5:38 AM

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

Show quoteHide quote

"Christian Blackburn" <christian.Blackb***@Yahoo.com> schreef in bericht
news:1149891333.699786.76970@h76g2000cwa.googlegroups.com...
> 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
>

11 Jun 2006 11:54 AM

Göran_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.

Christian Blackburn wrote:
Show quoteHide quote

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

12 Jun 2006 2:04 PM

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

Show quoteHide quote

> 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.
>
> Christian Blackburn wrote:
>
>> 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

12 Jun 2006 3:14 PM

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

Show quoteHide quote

> 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.
>> Christian Blackburn wrote:
>>
>>> 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

12 Jun 2006 4:42 PM

Göran_Andersson

Jim Wooley wrote:

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

Show quoteHide quote

> 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.
>>
>> Christian Blackburn wrote:
>>
>>> 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
>
>