Solved

SQL, Between dates and SQL Injection

Posted on 2007-12-04
10
490 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article I will describe the Copy Database Wizard 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 shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 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

10 Experts available now in Live!

Get 1:1 Help Now