Parsing SQL from ASP pages containing chars like " ' " etc.

[JulianUK]

Hi
I have an ASP page which accepts several address lines from text inputs. I have obvious problems if the user enters values contining apostrophes (') as they get passed to SQL Server as parameters for a stored proc, and the SQL falls over.

Is there a recognised way of parsing parameters, lumps of SQL etc. prior to passing over to SQL Server? I am currently thinking of using REPLACE (in VBS and/or SQL) to put in a ` where an ' occurs, or even to double up the ' chars to make them valid (although that gives probs by increasing the size of the text). Seems very laborious though, and there may be other such characters?

Many thanks for your help

Julian

 

[tlbroadbent]

In our VB and ASP applications, we use a function that replaces each embedded single quote with two single quotes before sending to SQL Server. SQL Server then stores the two single quotes as one. As you suggest, you can use the Replace function for this pppurpose. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[JulianUK]

Hi Terry
I implemented this using REPLACE on the ASP side and it worked well. And when it came down to it I couldn't actually think of any other characters that would make it fail anyway.

happy web app anyway.

thanks for the input.

Julian