Problems with single quotes/apostrophes in session variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problems with single quotes/apostrophes in session variables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-16 : 09:10:47

Dermot Waters writes "I just came across your site and I was wondering if you could help me with something. I'm building an ASP reservation form for a friend of mine. Unfortunately, if a user enters a single quote/apostrophe in any of the fields, the INSERT statement goes extremely haywire.

for instance, here is my insert statement (separated by single quotes):
sql="INSERT INTO
Customer(cardHoldersName,custEmail,custPhoneNumber,custStreet,custCity,custState,custZipCode,custCountry,creditCardNumber,creditCardExpDate,custMailList,custDate)"
&_
"VALUES('" & session("cardHoldersName") & "', '" & session("custEmail") & "',
'" & session("custPhoneNumber") & "', '" & session("custStreet") & "', '" &
session("custCity") & "', '" & session("custState") & "', '" &
session("custZipCode") & "', '" & session("custCountry") & "', '" &
session("creditCardNumber") & "', '" & session("creditCardExpDate") & "', '" &
session("custMailList") & "', '" & session("custDate") & "');"
conn.execute (sql)

Do you have any solution to this? Is there any VB script that can strip out apostrophe's from session variables? Please let me know. Thanks in advance!!

Dermot S. Waters
dermot@mindspring.com"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-16 : 09:31:11

Use the Replace Function in VB and Replace single quotes with 2 single quotes

Something like

Replace(@OriginalString,@CharToReplace, @ReplacementChar)
Replace(Session("cardHoldersName"),"'","''")

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources