Escape characters

[Maziar Aflatoun]

Hi everyone,

I have a form that stores the information it collects into a database.
However, for textboxes if I have a user input as something like
this 's 'sda, the ' causes it to fails (ex. Incorrect syntax near
's'...etc). Is there a function that would make this database safe?

Thank you
Maz.

 

[S. Justin Gengo]

Maziar,

If to pass an apostrophe into a database double up the apostrophe.

So If a user were to enter: 'sda

You would do this:

Dim StringForDatabase As String = TextBox1.Text.Replace("'", "''")

An enlargement of the quotes would look like this: " ' ", " ' ' "


--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche

 

[Jos]

Hi everyone,

I have a form that stores the information it collects into a
database. However, for textboxes if I have a user input as something
like
this 's 'sda, the ' causes it to fails (ex. Incorrect syntax near
's'...etc). Is there a function that would make this database safe?

Thank you
Maz.

Apart from Justin's suggestion, you can also use the Parameters
collection of the OleDbCommand or SqlCommand.

For instance: (this is for Visual Basic)
Dim strSQL As String =
"INSERT INTO myTable (Name,Address) VALUES (@Name,@Address)"
Dim cm As New OleDbCommand(strSQL,conn)
cm.Parameters.Add("@Name",nameFromUserInput)
cm.Parameters.Add("@Address",addressFromUserInput)
myList.DataSource=cm.ExecuteReader()

This code will take care of the quotes (note that it will also automatically
add quotes around string data in the SQL command).
It will convert DateTime input to the correct format for SQL as well.
On top of that, this code will also prevent hackers from inserting
unsafe commands into the SQL string.

 

[Jason S]

Maziar,

You should be concerned with SQL injection attacks (esp. if this is a public
facing site). If you are going to use dynamic sql strings like this you
should really be examining input closely before passing it to your database.
If you use stored procedures you will not have to worry much about this. Do
a google search on SQL injection attacks.

Regards,
Jason S.