• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

Preventing SQL Injection

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

Open in new window

  • 2
  • 2
1 Solution
Mark WillsTopic AdvisorCommented:
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...

huerita37Author Commented:
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.
Have a look at the "Resources" section on this site:

It explains how hackers use different techniques on this front for injections and xss (cross-site scripting) attacks.
Mark WillsTopic AdvisorCommented:
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 :

6) would imagine that it can be hardcoded .
huerita37Author Commented:
thank you very much for all of your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now