Solved

SQL, Between dates and SQL Injection

Posted on 2007-12-04
10
513 Views
Last Modified: 2012-05-05
Good afternoon,

I'm attempting to build a Select statement from my database to fill a Gridview.  I want it to pull the results from within the past 14 days.  I've got this working by doing the following:

I have a label, with visability set to false on the page.  On page load, I have it populate the labels.text with the date of today MM/DD/YYYY.  I then use the following in my where clause:
AND (Labor.LaborDate BETWEEN DATEADD(d, - 14, @Today) AND @Today)

@Today is obviously a control parameter that ties back to my label that stores the date.

Okay, now to my question - while researching how to effectively use between, I saw a lot of warnings about using dynamic SQL statements, because of SQL Injection attacks.  My understanding is this would be the case if I was taking this value from a text box, rather than an un-editable label that is also never visable.  Is this correct? Or would my current setup leave me open to SQL injection attacks?

Thanks in advance for your advice,

Dave
0
Comment
Question by:arhame
  • 5
  • 3
  • 2
10 Comments
 
LVL 18

Accepted Solution

by:
Yveau earned 250 total points
ID: 20407336
SQL injection is only a risk if you would take a field (textbox) from the client app and pass that on to the SQL Server in a dynamic SQL statement. So if the client app does something similar to:

    $SQL = "Select * from Mytable where MyField = " + $textbox1.text

If you work with stored procedure calls (as you should always do !) this should be safe:

    $SQL = "execute MyProc @MyParm = " + $textbox1.text

In that case the variable that can be influenced by the end user is interpreted as a parameter and not as part of a SQL Statement ... and that puts you in the safe zone !

Hope this helps ...
0
 
LVL 8

Author Comment

by:arhame
ID: 20407380
Okay, that makes sense.  So even though I'm not using a stored procedure, since it's pulling the value from a place that is not editable or changable by any user, then it's safe.

Okay, thanks for the clarification :)  All those articles got me worried, and I just wanted to verify I wasn't potentially making a huge mistake.
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 250 total points
ID: 20407383
CAUTION!! It's not that easy

    $SQL = "execute MyProc @MyParm = " + $textbox1.text

If my textbox.text is " ''; sp_adduser 'give', 'access'; "

You will be in trouble
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 8

Author Comment

by:arhame
ID: 20407400
Ahhh, yeah I can see how that could be a problem.  Thanks for the clarification for future implementations.  But as this situation I'm using a hidden label, I'm okay?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20407410
Yup. But that's an example of SQL Injection, if you needed one.  Using Stored Procedures doesn't help, you actually need to do it properly.
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20407419
Yep, you're right imitchie, oversaw that caveat for a second :-\

As long as the label cannot be modified by the end user you should be in the clear ...
Still not a nice implementation but ... for now it will do I guess ...

Hope this helps ...
0
 
LVL 8

Author Comment

by:arhame
ID: 20407429
Okay, thanks.

Split the points in half, since he answered my original question about labels being safe, and gave imitchie points for going in further depth on what a sql injection is.
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20407443
Glad I could be of any help.
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20407470
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20407481
...and probably the best one (in BOL !)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/scsql9/html/c04f1f33-d607-461b-9c75-799c55087a63.htm
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Linked Server Issue with SQL2012 3 26
Find results from sql within a time span 11 32
Query to capture 5 and 9 digit zip code? 4 21
SQL Query assistance 16 26
In this article I will describe the Backup & Restore 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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 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