Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Preventing SQL Injection

Posted on 2008-10-04
5
Medium Priority
?
274 Views
Last Modified: 2012-05-05
I have a query that will be updating a table in the DB.  I want to prevent SQL Injection so I am using parameters.  MM_ActiveStartDate and MM_ActiveExpireDate are data type dateTime in theDB but I don't know how to create that parameter.  

Is the below the correct statement?


MM_ActiveUser = "1"			
MM_ActiveStartDate = Today
MM_ActiveExpireDate = DateAdd("m",howManyMonths,Today)
Set UpdateDates = Server.CreateObject ("ADODB.Command")
UpdateDates.ActiveConnection = MM_indie_STRING
UpdateDates.CommandText = "UPDATE IR.Users  SET ActiveUser = ?, FreeMovies = ?, ActiveStartDate = ?, ActiveExpireDate = ? WHERE UserID = ?"
UpdateDates.Prepared = true                           UpdateDates.Parameters.Append UpdateDates.CreateParameter("param1", 5, 1, -1, MM_ActiveUser) ' adDouble
UpdateDates.Parameters.Append UpdateDates.CreateParameter("param2", 5, 1, -1, UserInfo.Fields.Item("FreeFilmsProvided").Value) ' adDouble
 UpdateDates.Parameters.Append UpdateDates.CreateParameter("param3", 202, 1, 50, MM_ActiveStartDate) ' adVarWChar
UpdateDates.Parameters.Append UpdateDates.CreateParameter("param4", 202, 1, 50, MM_ActiveExpireDate) ' adVarWChar
UpdateDates.Execute()
UpdateDates.ActiveConnection.Close

Open in new window

0
Comment
Question by:huerita37
  • 2
  • 2
5 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22643271
if possible express the datetime parameter in the format of yyyymmdd hh:mm:ss (24 hour) and you can simply express it as a string,. It is essentially the ONLY intrinsically understood implied date string without having to get into the convert function.

Also to prevent injections try to construct your where statement so that an inject string will not "finish" a statement - in the case above it would be easy to append a new sql command by appending a semicolon, then what ever command is wanted. So, use brackets, use semicolons, use seperate lines etc... Saw one guy who faithfully always had 'AND 1=1);' over a couple of lines to ensure a "known" closure. You seem to be missing param 5....

So, back to the date.... Object would be to achieve the following structure :

update is.users set activestartdate = '20081005 12:13:14'    --or
update is.users set activestartdate = convert(datetime, '10/05/2008 12:13:14', 101)

It maybe deisrable to always use the convert, kind of adds an additional check for injection in so much as the the convert will fail if not a legitimate date in the format as determined by the format number in the convert statement. To do that, will need to string together the equivelent of    "... convert(datetime,'"&mm_Activestartdate&"',nnn) ....."   where nnn is the format number that represent the layout of mm_ActiveStartDate e.g. 103 = dd/mm/yyyy, 101 = mm/dd/yyyy, 106 = dd MMM yyyy, 110 = mm-dd-yyyy, 112 = yyyymmdd, 120 = yyyy-mm-dd etc...

0
 

Author Comment

by:huerita37
ID: 22645777
I have a couple of questions:

1)  How would I use 'AND 1=1);' ?  Would I use it like this?  Putting a '(' at the beginning of the statement and closing it off with AND 1=1); ?  How does the AND 1=1); stop the SQL Injection?  If there is an extra semicolon would the query fail?  Is the below correct?

"(UPDATE IR.Users  SET ActiveUser = ?, FreeMovies = ?, ActiveStartDate = ?, ActiveExpireDate = ? WHERE UserID = ? AND 1=1);"
 
2) How would I use brackets, semicolon or seperate lines for the above?

3)  Using your example above would this be correct?
set MM_ActiveStartDate = convert(datetime, '" & Today & "', 112)

Then when I create the parameters would this be correct?
UpdateDates.Parameters.Append UpdateDates.CreateParameter("param3", 202, 1, 50, MM_ActiveStartDate) ' adVarWChar

4)  mm_Activestartdate is just Todays date.  Will I still need to worry about SQL Injection?

5) I am very new to programming to prevent SQL Injection.  Is there a site that you recommend?

6)  When updating, the 'ActiveUser' column will always be set to '1'.  Do I need to have a parameter for this or can I hardcode it in the statement?

Thank you for pointing out about my 5th parameter.
0
 
LVL 2

Expert Comment

by:devshb
ID: 22646614
Have a look at the "Resources" section on this site:
http://www.sqlinjectionscanner.com/

It explains how hackers use different techniques on this front for injections and xss (cross-site scripting) attacks.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 22648836
1) and 2) the bracket encapsulates all items within the "where" e.g. where ( userid=?  then a seperate concatenation for  " and 1=1" and to close off with " );" the idea being a statement that will fail unless specifically handled - there are other techniques as per the link above (have not seen it yet), and others...

3) most likely - need to be careful with dates - the convert(datetime,<datestring>,format) uses the format number to describe the layout of the <datestring> so convert knows how to make it into a datetime field. if format number and <datestring> do not match then you will get an error. mm/dd/yyyy is 101, dd/mm/yyy is 108, yyymmdd is 112 (and the only string date where you do not need a format number), dd MMM yyyy is 106 etc etc...
So the object is to make sure your string in a date format that complies with yyyymmdd

4) Yes, SQL injection can happen where ever there is an opportunity to embed a legitimate SQL command. If mm_ActiveStartDate is always "today" then you could use ' getdate() ' in place of the parameterised query for that column (ie have it "hard-coded") - also have a look at 3 above.

5) have a look at the above - there is also some Microsoft "How To" and such like : http://msdn.microsoft.com/en-us/library/ms161953(SQL.90).aspx and http://msdn.microsoft.com/en-us/library/aa175398(SQL.80).aspx then there are blogs like :
http://msmvps.com/blogs/harrywaldron/archive/2008/05/31/microsoft-best-practices-for-preventing-sql-injection-attacks.aspx

6) would imagine that it can be hardcoded .
0
 

Author Closing Comment

by:huerita37
ID: 31503115
thank you very much for all of your help.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question