Query string problem - Syntax error

[socoolbrewster]

I am submitting some input data to an Access database using a form. I need to allow people to use " and ' marks. How can allow them to do this and prevent the following error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''it's','8/7/2003 7:16:01 PM')'.
/socoolbrew/review.asp, line 102

I have tried the following:

Replace(formVar,"'",".")

But its doesn't seem to work as I get the following error:

Error Type:
Microsoft VBScript compilation (0x800A0414)
Cannot use parentheses when calling a Sub
/socoolbrew/review.asp, line 100, column 26
Replace(StrReview,"'",".")
-------------------------^

Any ideas guys?

[Staffa]

If I'm correct in guessing that formVar stands for
formVar = Request.Form("whatever")
then you should change the above to
formVar = Replace(Request.Form("whatever"),"'",".")
and just enter formVar where needed without anything else.
Give it a try

[socoolbrewster]

Thanks Staffa that works a treat. Out of interest are there any other character a user could input that I need to be wary of? other than '

[mattglet]

if you totally want to make sure no one tries to SQL hack you, you could disallow every character that's not allowed in email addresses.

i.e.

" ,! # $ % ^ & * ( ) = +

just to name a few... but you get what i mean. only allow alphanumeric plus . @ - _

-Matt

[socoolbrewster]

Thanks for the tip Matt

[Staffa]

You're welcome, I cann't think of anything to add to what Matt already said.

[socoolbrewster]

Might there be away whereby I can check for all of the following character in a string all at once with one statment?

Characters I need to for:

" ,! # $ % ^ & * ( ) = +

I thinking maybe an array?

[mattglet]

yes, looping through an array would work. or you could do something like the function below (*disclaimer: the following code has not been tested, or guarantees that it is efficient in any way... but, it just might be :) )

function checkforbadchars(string)
if (instr(1, string, chr(34), 1)) > 0 '--- looks for "
inString = 1 '--- the character was found
elseif (instr(1, string, chr(44), 1)) > 0 '--- looks for ,
inString = 1
elseif (instr(1, string, chr(33), 1)) > 0 '--- looks for!
inString = 1
elseif (instr(1, string, chr(35), 1)) > 0 '--- looks for #
inString = 1
elseif (instr(1, string, chr(36), 1)) > 0 '--- looks for $
inString = 1
elseif (instr(1, string, chr(37), 1)) > 0 '--- looks for %
inString = 1
elseif (instr(1, string, chr(94), 1)) > 0 '--- looks for ^
inString = 1
elseif (instr(1, string, chr(38), 1)) > 0 '--- looks for &
inString = 1
elseif (instr(1, string, chr(42), 1)) > 0 '--- looks for *
inString = 1
elseif (instr(1, string, chr(40), 1)) > 0 '--- looks for (
inString = 1
elseif (instr(1, string, chr(41), 1)) > 0 '--- looks for )
inString = 1
elseif (instr(1, string, chr(61), 1)) > 0 '--- looks for =
inString = 1
elseif (instr(1, string, chr(43), 1)) > 0 '--- looks for +
inString = 1
else
inString = 0 '--- none of the characters are in your string
end if

checkforbadchars = inString '--- return the value
end function

then after your function completes, you can check to see if whatever = 1 or 0

hope this helps.

-Matt

[aspdaddy]

Can a regex do this?, I dont know much about them but looks a good candidate.

Another approach would be create a string called "badchars" and implement a partial match function . Its something like below, not got time to think about it too much :)

HTH

function ispartialMatch (byval badchars,byval mystring)

result=false // assume failure
for i=0 to len(badchars)
for j=0 len(mystring)
if mid(badchars,i,1) = mid(mystring,j,1) then
ispartialMatch=true: exit for
end if
next:next:end function

[mattglet]

yeah, a regex would work too... man i need to look into those things. it seems like everyone talks about them now. i have no idea how to implement them. :(

-Matt