Solved

Preventing SQL Injection

Posted on 2008-10-04
5
264 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 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now