Trapping Single Quotation Mark

[M P]

Hi!

I am looking for a way that I can trap the single quotation mark. If an
encoder uses single quotation mark on a textbox field, it always give me an
error because I use single quotes on the SQL statement. Can you help trap
this character not to produce error?

Me

 

[Evertjan.]

M P wrote on 24 nov 2005 in microsoft.public.inetserver.asp.general:

I am looking for a way that I can trap the single quotation mark. If
an encoder uses single quotation mark on a textbox field, it always
give me an error because I use single quotes on the SQL statement. Can
you help trap this character not to produce error?

t = replace(t, "'","`")

btw, the single quote/apostrophe won't ALWAYS give you an error.
Not when used by a hacker to gain entry to your server.

 

[Bob Barrows [MVP]]

Hi!

I am looking for a way that I can trap the single quotation mark. If
an encoder uses single quotation mark on a textbox field, it always
give me an error because I use single quotes on the SQL statement.
Can you help trap this character not to produce error?

Me

The (to me) simple answer is to stop using dynamic sql and start using
parameters.
Either via saved parameter queries (Access):
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd

Stored procedures (SQL Server):
http://tinyurl.com/jyy0

or, if you can't bring yourself to try either of the above, via an explicit
Command object used to pass parameters to a string containing ODBC parameter
markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

The explanation for Evertjian's "hackers" remark can be found here:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23


Bob Barrows

 

[MyndPhlyp]

Hi!

I am looking for a way that I can trap the single quotation mark. If an
encoder uses single quotation mark on a textbox field, it always give me an
error because I use single quotes on the SQL statement. Can you help trap
this character not to produce error?

Two general rules of thumb:

* Test for what is allowed rather than what is not allowed.
* Use parameterized SQL.

Walk the string, character by character, testing for allowed characters. For
example:

Function IsGoodString(ByVal str)
Const strGoodChars = "abcdABCD0123" ' Allowed chars
Dim C
Dim I
IsGoodString = True
For I = 1 To Len(str)
C = Mid(str, I, 1)
If (InStr(strGoodChars, C) = 0) Then ' Not found
IsGoodString = False
Exit For
End If
Next
End Function

Here is a brief white paper on securing ASP pages that you might find
interesting:

http://www.ngssoftware.com/papers/asp.pdf

One thing you will soon notice is that embedded quotes are only the
beginning of the problem. A good general purpose solution is to use
parameterized SQL. You will also find that white paper contains links to
other white papers of interest. A lot has been written on the subject of SQL
injection and how to prevent such attacks.

If, after reading that white paper, you still do not want to use
parameterized SQL you can "escape" delimiting characters (such as single
quotes) by using the Replace function:

strNewString = Replace(strOldString, "'", "''")

 

[Bob Lehmann]

Walk the string, character by character,
Yuk!

Wouldn't regular expressions be a skosh more efficient?

Bob Lehmann

 

[Evertjan.]

MyndPhlyp wrote on 24 nov 2005 in microsoft.public.inetserver.asp.general:

Function IsGoodString(ByVal str)
Const strGoodChars = "abcdABCD0123" ' Allowed chars
Dim C
Dim I
IsGoodString = True
For I = 1 To Len(str)
C = Mid(str, I, 1)
If (InStr(strGoodChars, C) = 0) Then ' Not found
IsGoodString = False
Exit For
End If
Next
End Function

<script language=jscript runat=server>
function IsGoodString(str){
return !/[^abcd0123]/i.test(str)
}
</script>

Testing:<br>

<%
response.write IsGoodString("Abba")
response.write "<br>"
response.write IsGoodString("Zoef")
%>

 

[MyndPhlyp]

Yuk!

Wouldn't regular expressions be a skosh more efficient?

Eh. It is all a matter of personal style. They both accomplish the same
task. Depending on your myndset one method is more easily recognized than
the other. The difference in overhead is going to be negligible. I tend to
fall back to really old school methods since it can be recognized by
virtually all levels of programming experience. Besides, it is just an
example and I prefer to leave the art of computer programming and
efficiencies/inefficiencies to Donald E. Knuth
http://www-cs-faculty.stanford.edu/~knuth/.

(Hey - he finally got Volume 4 published!)

 

[Bob Lehmann]

They both accomplish the same task.
So, given the task of moving several tons of rocks, you would suggest using
a wheelbarrow, since it is easier to understand, and use, than a front-end
loader?

VB(Script) is notorious for inefficient string handling. I expect that on a
string of even 200+ bytes there would be a significant, noticable difference
between your method and a regular expression.

Bob Lehmann

 

[Larry Randolf]

bla blah blah

So, given the task of moving several tons of rocks, you would suggest
using
a wheelbarrow, since it is easier to understand, and use, than a front-end
loader?

VB(Script) is notorious for inefficient string handling. I expect that on
a
string of even 200+ bytes there would be a significant, noticable
difference
between your method and a regular expression.

Bob Lehmann

 

[Bob Lehmann]

My, aren't you a clever one!

It is also impressive how willing you are to put your ignorance on public
display.

Keep up the good work!

Bob Lehmann