Inserting a value that MIGHT have a single quote

[Autosys]

Hi there ..

I have a small problem which I'm sure would be easy for some of you to solve and any help would be greatly appreciated.

I have a little Web Based Java Application which will insert some data into a column in a table in a SQL Server 2000 Database. The value could be anything "text string" that the user types (varchar).

My SQL Statement is in the App and it looks something like this: insert into tablename (column1) values ("+java_variable+")

The problem is that it fails if the java_variable has a single quote in it. I.E the text "apples" will insert fine but "stephen's" will fail.

I understand that in SQL Server you can avoid this by adding additional single quotes but this only works when you know if and when there will be a single quote. Is this something that SQL can fix/get around? Appreciate that I might have to change the java code if there is no solution.

Many Thanks!
S

 

[ca8msm]

You should use a Stored Procedure and add the values as a parameter as this will protect you against SQL Injection.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[r937]

use the java language (disclaimer: i don't know how) to replace each single quote in java_variable with two consecutive single quotes, before incorporating into the INSERT statement

r937.com | rudy.ca

 

[chrissie1]

And the jave code should use a prepared statement.

So this

insert into tablename (column1) values ("+java_variable+")

becomes this

insert into tablename (column1) values (?)

Sorry rudy but what you suggest is bad practice. ;-)


Christiaan Baes
Belgium

"My old site" - Me