Making user input safe

[Stephen Adam]

Hi there,

I am working on a project were a user can update a SQL Server DB via a web
form. I've included a number of text boxes for a user to enter in strings.
The text from these boxes is then used in an update command. My problem is
if the user enters any speech marks then it will break the code as they are
interpretted as the end of the SQL statement. I'm sure there are other
characters which will also cause problems.

Here is a code snippet.
sSQL = "INSERT INTO t_links VALUES (" & "'" & tbLinkName.Text() & "'" & ","
& "'" & tbLinkAddress.Text() & "'" & "," & "'" & tbLinkDescription.Text() &
"'" & ")"

Is there any automated way of turning the contents of these text boxes into
their literal form so the contents can be safely used in this way?

In PHP you've got HTMLSpecialChars function which will turn script/html into
the actual text we want. Is there anything like this I can use for VB.net?

Thanks

Steve

 

[CHRISM]

Hi there,

I am working on a project were a user can update a SQL Server DB via a web
form. I've included a number of text boxes for a user to enter in strings.
The text from these boxes is then used in an update command. My problem is
if the user enters any speech marks then it will break the code as they are
interpretted as the end of the SQL statement. I'm sure there are other
characters which will also cause problems.

Here is a code snippet.
sSQL = "INSERT INTO t_links VALUES (" & "'" & tbLinkName.Text() & "'" & ","
& "'" & tbLinkAddress.Text() & "'" & "," & "'" & tbLinkDescription.Text() &
"'" & ")"

Is there any automated way of turning the contents of these text boxes into
their literal form so the contents can be safely used in this way?

In PHP you've got HTMLSpecialChars function which will turn script/html into
the actual text we want. Is there anything like this I can use for VB.net?

Thanks

Steve

You want to change single quotes into two single quotes..

Instead of using:

tbLinkName.Text()

Try using:

tbLinkName.Text().Replace("'","''")


Same for any other text data that may contain single quotes..

// CHRIS

 

[Ken Dopierala Jr.]

Hi Steve,

The first thing I would recommend is using Stored Procedures. This is
faster and safer and you don't have to worry about things like apostrophes
and quotes. The way you are doing it now a user could go to your site and
perform a SQL injection to knock out your database or worse steal all your
information. To help protect against this doing it your way, make sure you
limit the number of characters that can be typed into each textbox. For
your solution I suggest writing a routine that takes a string and makes it
SQL compatible. Something that adds an apostrophe if there is only one and
etc. Then when you create your SQL statement use this routine for each text
box:

"Insert Into......" + SafeSQL(tbLinkName.Text) + "....."

Good luck! Ken.

 

[Herfried K. Wagner [MVP]]

Here is a code snippet.
sSQL = "INSERT INTO t_links VALUES (" & "'" & tbLinkName.Text() & "'" & ","
& "'" & tbLinkAddress.Text() & "'" & "," & "'" & tbLinkDescription.Text() &
"'" & ")"

Use an '*InsertCommand' + parameters instead:

Using Parameters with a 'DataAdapter'
<URL:http://msdn.microsoft.com/library/en-us/cpguide/html/cpconusingparamete
rswithdataadapters.asp>

 

[Stephen Adam]

Thanks for the advice guys

Got the double quotes sorted now and will look into using stored procedures,
SQL injection looks pretty nasty!

Cheers

Steve