Solved

Preventing SQL Injection

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

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…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

864 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

20 Experts available now in Live!

Get 1:1 Help Now