“Don’t forget to sanitize your data inputs”

Posted: July 3, 2008 in SQL Server 2008
Tags: , ,

I was looking at a friend’s IM message today and it said “don’t forget to sanitize your data inputs”. I got to thinking about how I will put up stupid NASCAR sayings just to poke fun at another friend of mine.  The more that I sat down and thought about it I realized the importance of the statement that my friend was making.  So the question is to why should I sanitize my data inputs, and what does that really mean?

When you create a stored Procedure the format you use will look like this:

Create Procedure ProcName

@Var1 (varchar)

AS

Select Name from PhoneBook

Where name like @var1

Go

If you are new to stored procedures the data input here is the “@Var1”, this is the place where the users request is going to be sent to the database.  For example let’s say that this stored procedure is called from a web page.  A user could select to see if there was someone in the database named “Chris Shaw” or “Bob Smith”.  Why would we want to sanitize it? Some people may think that we would be limiting the search results by doing this.

What is sanitizing? Well its making sure that any value that is inserted into that @Var1 is data that we expect it to look like.  An Example of this would be to make sure that there is not a ‘;’ in the var.  I am not sure I have ever seen anyone with a semi-colon in the name.  These non useful characters need to be pulled out and/or a message that this input did not produce any results.

Well the end results are this:

  • Keep your data clean – The example I gave you was a search and not in insert, but if it were an insert the end result is that you don’t want data in your database that is not sharp, accurate and clean.
  • SQL Injection – Many developers in the past and still today will put the SQL code into the web page itself. This has caused a lot of issues but ads a lot of flexibility, the end result is no matter where the input is; if you are not checking to make sure that data is clean you are at risk.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s