SQL Update/Insert - problem with Apostrophes

[Guest]

Hi there - I'm sure there is a simple answer to this ?!? - I just don't know
it...

I have several text boxes on an asp.NET web page that are used to gather
information from the user and then sent to a SQL database - they are
basically editing new or existing records.

When any text is entered in one of the free text boxes and the user uses an
' (such as - didn't manage to get through - the insert or update statement to
SQL treats the apostrophe as a break in the statement....

e.g.
Dim UserAnswer as String = txtUserAnswer.Text

Dim updateCmd as string = "Update DATABASE Set ANSWER = ' " & UserAnswer & "
' "

(obviously there are not really all the spaces between the " and ' but I
have put them in to highlight what I have in what order

If I then use updateCmd as the SQL update command and they have used an
apostrophe in the text box, the update fails with incorrect syntax in SQL
statement or something similar - obviously if no apostrophe is used it works
fine...

Your thoughts & advice are appreciated

Stuart

 

[Guest]

Stuart,

You need to use parameters to supply the variable data to the sql statements.

Not only will parameters deal with the apostrophe problem you are having
now, they will also deal with the sql injection problem you will undoubtedly
be having in the near future.

Kerry Moorman