apostrophes and sql

zkiller · 03-14-2005, 04:22 AM

well, in asp you can have a user put a apostrophe in a field as it will recognize it as being the end of a field and create an error. however there are ways around this. since i am lazy, i decided to search for a function to take care of this little problem for me instead of writting my own. i found one which i liked and promptly integrated it. it works great on all fields of the user profile on our site, but two. i am to tired to figure it out right now as to why it's not working... maybe tomorrow. :shrug: anyways, just thought i would share. someone on sitepoint forums posted this.

function:

Code:

	Public Function CleanQuotes( ByVal strDirty, boolRemove )
		strData = Replace( Replace( strDirty, Chr(147), Chr(34) ), Chr(148), Chr(34) )
		strData = Replace( Replace( strData, Chr(146), Chr(39) ), Chr(96), Chr(39) )
		If Not CBool( boolRemove ) Then
			strData = Replace( strData, Chr(39), String( 2, Chr(39) ) )
			strData = Replace( strData, Chr(34), Chr(39) & Chr(34) )
		ElseIf CBool( boolRemove ) Then
			strData = Replace( strData, Chr(39), Chr(32) )
			strData = Replace( strData, Chr(34), Chr(32) )
			Do While InStr( 1, strData, String( 2, Chr(32) ) ) > 0

				strData = Replace( strData, String( 2, Chr(32) ), Chr(32) )
			Loop
		End If
		CleanQuotes = Trim( strData )
	End Function

example for using it:

Code:

	' to escape for SQL statements
	strClean = CleanQuotes( strDirtyData, False )
	' to remove completely
	strClean = CleanQuotes( strDirtyData, True )

03-14-2005, 04:22 AM	#1 (permalink)
zkiller Super Noob Join Date: Aug 2004 Location: position:relative Posts: 1,549	apostrophes and sql well, in asp you can have a user put a apostrophe in a field as it will recognize it as being the end of a field and create an error. however there are ways around this. since i am lazy, i decided to search for a function to take care of this little problem for me instead of writting my own. i found one which i liked and promptly integrated it. it works great on all fields of the user profile on our site, but two. i am to tired to figure it out right now as to why it's not working... maybe tomorrow. :shrug: anyways, just thought i would share. someone on sitepoint forums posted this. function: Code: Public Function CleanQuotes( ByVal strDirty, boolRemove ) strData = Replace( Replace( strDirty, Chr(147), Chr(34) ), Chr(148), Chr(34) ) strData = Replace( Replace( strData, Chr(146), Chr(39) ), Chr(96), Chr(39) ) If Not CBool( boolRemove ) Then strData = Replace( strData, Chr(39), String( 2, Chr(39) ) ) strData = Replace( strData, Chr(34), Chr(39) & Chr(34) ) ElseIf CBool( boolRemove ) Then strData = Replace( strData, Chr(39), Chr(32) ) strData = Replace( strData, Chr(34), Chr(32) ) Do While InStr( 1, strData, String( 2, Chr(32) ) ) > 0 strData = Replace( strData, String( 2, Chr(32) ), Chr(32) ) Loop End If CleanQuotes = Trim( strData ) End Function example for using it: Code: ' to escape for SQL statements strClean = CleanQuotes( strDirtyData, False ) ' to remove completely strClean = CleanQuotes( strDirtyData, True ) __________________ Stefan, the Post Master Track Devil - May the boost be with you!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode