replacing single quotes in SQL statements

I am running a series of conn.execute statements on ASP pages against a
database. The SQL strings contain single quotes (') as value delimiters -
i.e: "INSERT INTO mytable (name, lastname) VALUES ('John', 'Smith') - which
is a problem when the values contain single quotes themselves, because of
the errors that can occur. Somehow I think the problem can be resolved by
way of the Replace() function, but I am not quite sure how. Any suggestions
would be greatly appreciated.

Thanks

TB

Re: replacing single quotes in SQL statements

TB wrote:
> I am running a series of conn.execute statements on ASP pages
> against a database. The SQL strings contain single quotes (') as
> value delimiters - i.e: "INSERT INTO mytable (name, lastname) VALUES
> ('John', 'Smith') - which is a problem when the values contain single
> quotes themselves, because of the errors that can occur. Somehow I
> think the problem can be resolved by way of the Replace() function,
> but I am not quite sure how. Any suggestions would be greatly
> appreciated.
>
Make it easy on yourself, as well as making your application more secure:
use parameters instead of dynamic sql:

dim arParms(1),cn,cmd,sSQL
arParms(0) = request.form("name")
arParms(1)=request.form("lastname")

'put code here to validate that the array contains valid data, then:

sSQL="INSERT INTO mytable (name, lastname) VALUES (?,?)"
set cn=createobject("adodb.connection")
cn.open ""
set cmd=createobject("adodb.command")
cmd.CommandText=sSQL
Set cmd.ActiveConnection=cn
cmd.Execute ,arParms,129
cn.close:set cn=nothing

See? No worries about quotes, sql injection, concatenation ...

If I can't convince you, then yes, you can use Replace to escape your
apostrophes by replacing them with two apostrophes:

dim sName
sName = Replace(request.form("name"), "'", "''")

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: replacing single quotes in SQL statements

You defintely convinced me. Now I just have to figure what you actually
did........

Thanks

"Bob Barrows [MVP]" wrote in message
news:OtZi57LIFHA.3376@TK2MSFTNGP14.phx.gbl...
> TB wrote:
>> I am running a series of conn.execute statements on ASP pages
>> against a database. The SQL strings contain single quotes (') as
>> value delimiters - i.e: "INSERT INTO mytable (name, lastname) VALUES
>> ('John', 'Smith') - which is a problem when the values contain single
>> quotes themselves, because of the errors that can occur. Somehow I
>> think the problem can be resolved by way of the Replace() function,
>> but I am not quite sure how. Any suggestions would be greatly
>> appreciated.
>>
> Make it easy on yourself, as well as making your application more secure:
> use parameters instead of dynamic sql:
>
> dim arParms(1),cn,cmd,sSQL
> arParms(0) = request.form("name")
> arParms(1)=request.form("lastname")
>
> 'put code here to validate that the array contains valid data, then:
>
> sSQL="INSERT INTO mytable (name, lastname) VALUES (?,?)"
> set cn=createobject("adodb.connection")
> cn.open ""
> set cmd=createobject("adodb.command")
> cmd.CommandText=sSQL
> Set cmd.ActiveConnection=cn
> cmd.Execute ,arParms,129
> cn.close:set cn=nothing
>
> See? No worries about quotes, sql injection, concatenation ...
>
> If I can't convince you, then yes, you can use Replace to escape your
> apostrophes by replacing them with two apostrophes:
>
> dim sName
> sName = Replace(request.form("name"), "'", "''")
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: replacing single quotes in SQL statements

Start here:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdobjcomm and.asp

Follow the links to here:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdobjcomm andpme.asp

Then here:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthcmde xecute.asp

Bob Barrows


TB wrote:
> You defintely convinced me. Now I just have to figure what you
> actually did........
>
> Thanks
>
> "Bob Barrows [MVP]" wrote in message
> news:OtZi57LIFHA.3376@TK2MSFTNGP14.phx.gbl...
>> TB wrote:
>>> I am running a series of conn.execute statements on ASP pages
>>> against a database. The SQL strings contain single quotes (') as
>>> value delimiters - i.e: "INSERT INTO mytable (name, lastname)
>>> VALUES ('John', 'Smith') - which is a problem when the values
>>> contain single quotes themselves, because of the errors that can
>>> occur. Somehow I think the problem can be resolved by way of the
>>> Replace() function, but I am not quite sure how. Any suggestions would
>>> be greatly
>>> appreciated.
>>>
>> Make it easy on yourself, as well as making your application more
>> secure: use parameters instead of dynamic sql:
>>
>> dim arParms(1),cn,cmd,sSQL
>> arParms(0) = request.form("name")
>> arParms(1)=request.form("lastname")
>>
>> 'put code here to validate that the array contains valid data, then:
>>
>> sSQL="INSERT INTO mytable (name, lastname) VALUES (?,?)"
>> set cn=createobject("adodb.connection")
>> cn.open ""
>> set cmd=createobject("adodb.command")
>> cmd.CommandText=sSQL
>> Set cmd.ActiveConnection=cn
>> cmd.Execute ,arParms,129
>> cn.close:set cn=nothing
>>
>> See? No worries about quotes, sql injection, concatenation ...
>>
>> If I can't convince you, then yes, you can use Replace to escape your
>> apostrophes by replacing them with two apostrophes:
>>
>> dim sName
>> sName = Replace(request.form("name"), "'", "''")
>>

--
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"