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

Is there a built in command to encode SQL strings?

P: n/a
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

Jun 9 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
"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

Jun 9 '06 #2

P: n/a
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

"Christian Blackburn" <ch*****************@Yahoo.com> schreef in bericht
news:11*********************@h76g2000cwa.googlegro ups.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

Jun 10 '06 #3

P: n/a
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

Jun 11 '06 #4

P: n/a
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

Jun 12 '06 #5

P: n/a
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.
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

Jun 12 '06 #6

P: n/a
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.
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


Jun 12 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.