Always use Replace with Strings?

[SteveDingle]

Heya All,

I'm starting to run into problems where I am passing in string parameters into a Stored Procedure that MAY contain a single quote.

EXECUTE Blah @String = 'this is my o'malley'

I started thinking of handling it in the client application but then wondered if it might not be better pratice to ALWAYS use Replace(String,"'","''") on ANY string parameter I may get in since I can't be 100% sure then only application calling the SP will be mine

Would appreciate any and all thoughts.

Toodles,
Steve Dingle

http://www.dsbusinesssolutions.co.uk

D&S Business Solutions Ltd

 

[jasonlhoward]

Hi Steve,

I don't know what language you're using but I wrote this little vbscript function that I run on all user-entered data that will be used by SQL. It will make sure that exactly two single quotes exist where as many as 100 existed previously but you can change this to a more sane level of course. I've included two more versions of the function as well.

Function StSS(str)
	dim j
	dim i
	dim brStr
	for j = 100 to 1 step -1
		brStr = ""
		for i = 1 to j
			brStr = brStr & "'"		
		next
		str = replace(str,brStr,"'")	
	next
	StSS = trim(replace(str, "'", "''"))
End Function

function SQLText(val)
  if IsNull(val) then
  	SQLText = "null"
  else
    SQLText = "'" & Replace(val, "'", "''") & "'"
  end if
end function

Function StSS(str)
	StSS = replace(str, "'", "''")
End Function

This is a good algoritm for eliminating excessive vbCRLF's in textarea input fields too (where the user hits ENTER a bunch of times).

Jason

 

[SteveDingle]

Heya Jason,

Thanks for the code, I'll actually be access this database using either a VFP or .Net application (both) however since I don't know what might be accessing it(them) in the future (I am working on this for a client as opposed to my company) I was hoping to deal with the issue in the SPs themselves

Thanks
Steve

 

[collierd]

I would use ` rather than '
e.g. this is my o`malley

I've always used this by way of 'best practice' within the SQL environment

Damian.

 

[SteveDingle]

Hello Damian,

Thanks that does sound like a good 'best practice' but again unforunately that would assume I always have control over what is sent into any SP.

Also I'm not sure I could always "sell" that to a client,(i.e. they have a different standard in storing the data)

But again, great idea that I hadn't thought of

Thanks
Steve