Replace command no longer needed? 1

[bigfoot]

Whenever a user used an ' in a textbox, SQL would crash. I came across a Replace command that will replace the ' with a ''.

I just tried SQL 2000 without anything and I entered "Gary's Toys" as a company name, and SQL took it without a hassle. I was using stored procedures with parameters too.

Would that make a difference??

 

[JamesLean]

Yes, there is a BIG difference between sending it as a parameter and concatenating it into a string.

The only reason they cause so much trouble is that SQL uses single quotes to delimit char data in a query. So when you build a string like:

Dim myvar As String = "gary's toys"

strSQL = "INSERT mytable (col1) VALUES ('" & myvar & "')"

You get "INSERT mytable (col1) VALUES ('gary's toys')" as the resultant string, which obviously errors when SQL tries to execute it.

When you pass in parameters, you are actually passing the value of the string and you aren't using delimiters in the same way, so you can basically pass in anything.

I hope this makes sense, it's quite hard to explain on paper! ;-)

--James

 

[bigfoot]

Yes. It did make sense. Is this going to bite me in the butt when I go to search on the field later.

Baybe I should use the replace command.

I used to do in line queries all the time before I learned how to do parameters.

 

[JamesLean]

Is this going to bite me in the butt when I go to search on the field later.

No, because the exact same value ends up in the DB whichever method you use to insert it: "gary's toys".

Generally, it is much better to use stored procs and pass in params when accessing the DB rather than use inline SQL. You get better performance, increased security and you don't have to guard against SQL injection attacks.

--James

 

[Rhys666]

You could also use JavaScript and the Client Side OnKeyPress event to stop control characters being entered into text boxes as well. But often that's a Business decision as to whether it's reasonable to exclude entry of certain characters within 'their' application.
Just a thought...

Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!