apostrophes and sql

[zkiller]

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:

	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:

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