How to filter a tic (" ' ") or any other character before posting 1

[Snappy2873]

Good morning,
I currently have a form with fields that posts data to a SQL DB. It works great but bombs on a tic mark (" ' "). I created the following function(in asp include file..) to filter the fields but I cant seem to get it to work:

function FilterQuotes(strSQL)
if not isnull(strSQL) then
if len(trim(strSQL)) then
strInput = Replace(strSQL,"'","")
strInput = Replace(strSQL,"""","")
strInput = Replace(strSQL,"*","")
end if
end if
FilterQuotes = strSQL
end function

Any recommendations would be greatly appreciated.
Thanks in advance,
Snappy

 

[Sheco]

I bet you'll find the problem if you debug like this:
[tt]
function FilterQuotes(strSQL)
[red]Response.Write "Debug SQL Begin: " & strSQL & "<BR>"[/red]
if not isnull(strSQL) then
if len(trim(strSQL)) then
[red]Response.Write "Debug SQL Middle: " & strSQL & "<BR>"[/red]
strInput = Replace(strSQL,"'","")
strInput = Replace(strSQL,"""","")
strInput = Replace(strSQL,"*","")
end if
end if
FilterQuotes = strSQL
[red]Response.Write "Debug SQL End: " & strSQL & "<BR>"[/red]
end function
[/tt]

 

[DotNetGnat]

what is the actual error you are getting?

-DNG

 

[Snappy2873]

I have the function in a global include file that affects all pages. I called my string in (strSQL) to debug and it just doesnt do anything. Do I need to specifically call the function( "call filterqoutes(strSQL)") in my process page even though it's in an include file? The idea is to replace the charater in question anywhere in the string, not just at the beginning. The reason I say this is because I already have javascript validating individual tic marks(by themselves) entered into a text field on my search page but it doesnt replace any that are in the middle of a string (hou'se) or ones that are at the beginning('house) or end(house') of the string.

 

[Sheco]

You'll want to call this function immediately before executing the SQL.

So if you have some line like this:
[tt]Set rs = cn.Execute(strSQL)[/tt]

Then you can do something like this:
[tt]Set rs = cn.Execute(FilterQuotes(strSQL))[/tt]

Or, perhaps more straightforward using two lines:
[tt]strSQL = FilterQuotes(strSQL)
Set rs = cn.Execute(strSQL)[/tt]

 

[Snappy2873]

Hey Sheco,
here's my setup:

1. I define what strSQL does

strSQL = "qparmInsertDocument '" & _
intNextID & _
"','" & CStr(Request.Form("txtvolnumber")) & _
"','" & CStr(Request.Form("txtdoctype")) & _
"','" & CStr(Request.Form("txtdoctitle")) & _
"','" & CStr(Request.Form("txtarkeyword")) & _
"','" & CStr(Request.Form("txtarsummary")) & _
"','" & CStr(Request.Form("txtarreldoc")) & _
"','" & CDate(Request.Form("txtdatereq")) & _
"','" & CStr(Request.Form("txtassby")) & _
"','" & CStr(Request.Form("txtdateappr")) & _
"','" & CStr(Request.Form("txtreceivedby")) & _
"','" & CStr(Request.Form("txtlocation")) & _
"','" & CStr(Request.Form("txtresponsible")) & _
"','" & CStr(Request.Form("txtauthor")) & _
"','" & CStr(Request.Form("txtarref")) & _
"','" & CStr(Request.Form("txtdeptnumber")) & _
"','" & CStr(Request.Form("txthardcopy")) & _
"'," & cint(blnAuto)

2. I connect to my DB
call ConnectDB()

3. I set my string equal to my function
strSQL = FilterQuotes(strSQL)
4. The function is called to do some work:

function FilterQuotes(strSQL)
RW "Debug SQL Begin: " & strSQL & "<BR>"
if not isnull(strSQL) then
if len(trim(strSQL)) then
'RW "Debug SQL Middle: " & strSQL & "<BR>"
strSQL = Replace(strSQL,"'","")
strSQL = Replace(strSQL,"""","")
strSQL = Replace(strSQL,"*","")
end if
end if
FilterQuotes = strSQL

'RW "Debug SQL End: " & strSQL & "<BR>"
end function

5. once the work is done, strSQL is executed.
objCONN.Execute strSQL

6. it bombs on step #5

 

[Sheco]

bombs how?

What do the debugging response.writes show ?

 

[Snappy2873]

Here's the error:

Debug SQL Begin: qparmInsertDocument 'TX213.060124.18','','',''','','','','1/24/2006','','1/24/2006','','','','test','','21311','',-1

Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near '.060124'.

/techlibrary/docconfirm.asp, line 70

 

[Sheco]

Well you have the other response.write functions all commented out.

But I suspect what is happening is that you actually NEED those single quotes because they are needed for delimitting string values in your SQL statement.

If this is true you will need to change the approach and call your function earlier instead of immediately before the SQL is executed.... somethng mroe like this:
[tt]
strSQL = "qparmInsertDocument '" & _
intNextID & _
"','" & [red]FilterQuotes([/red]CStr(Request.Form("txtvolnumber"))[red])[/red] & _
"','" & [red]FilterQuotes([/red]CStr(Request.Form("txtdoctype"))[red])[/red] & _
"','" & [red]FilterQuotes([/red]CStr(Request.Form("txtdoctitle"))[red])[/red] & _
.....
[/tt]

 

[Snappy2873]

Hey Sheco,
It works like a charm now, thanks for your help.
Snappy