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.

--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)

[Bob Barrows [MVP]]

M P wrote:

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&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl

http://groups-beta.google.com/group/...d322b882a604bd

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

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

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[MyndPhlyp]

"M P" <ma**@textguru.ph> wrote in message
news:ed*************@TK2MSFTNGP11.phx.gbl...

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

"MyndPhlyp" <no****@homeright.now> wrote in message
news:ux**************@TK2MSFTNGP11.phx.gbl...

"M P" <ma**@textguru.ph> wrote in message
news:ed*************@TK2MSFTNGP11.phx.gbl...

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, "'", "''")

[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")
%>

--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)

[MyndPhlyp]

"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:uQ**************@tk2msftngp13.phx.gbl...

Walk the string, character by character,

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.

Depending on your myndset one method
is more easily recognized than the other.
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

"MyndPhlyp" <no****@homeright.now> wrote in message
news:ui**************@TK2MSFTNGP10.phx.gbl...

"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:uQ**************@tk2msftngp13.phx.gbl...> Walk the string, character by character,

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

[Larry Randolf]

bla blah blah
"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:Oy**************@tk2msftngp13.phx.gbl...

They both accomplish the same task.
Depending on your myndset one method
is more easily recognized than the other.

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

"MyndPhlyp" <no****@homeright.now> wrote in message
news:ui**************@TK2MSFTNGP10.phx.gbl...

"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:uQ**************@tk2msftngp13.phx.gbl...

> >> Walk the string, character by character,
> 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]

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

"Larry Randolf" <la*********@hotmail.com> wrote in message
news:uX****************@TK2MSFTNGP14.phx.gbl...

bla blah blah
"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:Oy**************@tk2msftngp13.phx.gbl...

They both accomplish the same task.
Depending on your myndset one method
is more easily recognized than the other.

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

"MyndPhlyp" <no****@homeright.now> wrote in message
news:ui**************@TK2MSFTNGP10.phx.gbl...

"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:uQ**************@tk2msftngp13.phx.gbl...
> >> Walk the string, character by character,
> 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!)