SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Injection - Part 1


SQL Injection - Part 1

Author
Message
Randy_Dyess
Randy_Dyess
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/RDyess/sqlinjec



andrest
andrest
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 1
Nice article, but didn't Christoffer say all of that just 20 days ago? Check: http://www.sqlservercentral.com/columnists/chedgate/sqlinjection.asp

.
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7865 Visits: 3290

SQL Injection attacks happen due to a violation of basic programming principles.

As a matter of good programming practice

  • ALWAYS validate user input.
  • ALWAYS check that data cannot exceed its bounds i.e. the program won't try to store an SMALLINT in a BYTE field, or try and stuff a 11 byte string in a 10 byte space.
  • ALWAYS include error checking/exception handling.

I predate Microsoft so I can remember how damn grateful we were for software that was easy to use.

The emphasis in software development was to develop a tool that could be used. No one really looked at it from the "how could someone pervert this tool".

The sins of the fathers are truly been visited upon their sons. I dread to think how much code is in PC software per se that can be hacked due to unchecked bounds.



LinkedIn Profile

Newbie on www.simple-talk.com
Tatsu
Tatsu
Say Hey Kid
Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)

Group: General Forum Members
Points: 696 Visits: 307

Validating user input is really not complex at all. If you need to build and execute ad hoc queries you just need to remember two things.

With strings, always replace ticks/single-quotes (') with two ticks/single-quotes ('') <- notice there are two characters there and not a double-quote ("). If you do this then the user entry for that field will be accepted in its entirety and any SQL commands in the string, including comment markers.

Example:
User enters the following in password:
'; delete from users

You put the following code in the statement used to build SQL statement (VB 6)
sSQL = sSQL + "and password = '" & Replace (sPassword, "'", "''") & "'"

The resulting SQL statement is this:
select userid from users where userid = 'bogus' and password = '''; delete from users'

One other thing with strings. This is not so much a protection from SQL injection but will help keep errors from cropping up that could expose connection information in the form of an error response. Make sure you are limiting the size of strings in the UI to prevent people from entering text too large for the database to handle. If you are developing n-tier applications, you business layer should not trust the UI and double check the data.

For all non-string data you should use the appropriate means to determine if the input is valid for the data type that is to receive the input. If the field is an integer, check to see that the user entered an integer; same with dates, etc. Also don't assume that client-side form validation in web applications will catch this. Always check on the server-side because there is no guarantee that the user is using the form you created!



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
pe_johnson
pe_johnson
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 1
stored procedures, anyone?



Tatsu
Tatsu
Say Hey Kid
Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)

Group: General Forum Members
Points: 696 Visits: 307

pe johnson said "stored procedures, anyone?"

In my own defense, I did say "If you need to build and execute ad hoc queries" but maybe I did not stress the need enough. There are occasions where an application needs to throw together a query based on user input.

Stored procedures are the obvious choice for security and performance and should be used whenever possible. If you look around there are some tricks for handling situations where the obvious option is to use ad hoc queries (Particularly with WHERE clauses) but you really can use stored procedures with a little thought. If you want to get fancier, look into code generation (http://www.codegeneration.net/ and my favorite http://www.ericjsmith.com/codesmith). If you find yourself using the same patterns repeatedly, write a script or other tool to reproduce the code that results from the pattern and save yourself time down the road.



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
pe_johnson
pe_johnson
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 1

No harm, no foul. The comment was not really directed at anyone, I just wanted to introduce the concept of using stored procedures to short circuit injection attacks.

IMHO there is never a good reason to use dynamic sql, either as a command or wrapped in a stored procedure. Just create as many stored procedures as you need to handle the different scenarios, and call them appropriately.

Whenever I bring this approach up, development time up front seems to be the biggest pushback. But using this approach not only provides the security and performance benefits you mentioned, but also abstracts the internals of the db layer from the application/business layer. It also makes modifying application code as easy as calling a new sproc, instead of the nightmare of trying to maintain 300 lines of stored procedure code.





pe_johnson
pe_johnson
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 1

No harm, no foul. The comment was not really directed at anyone, I just wanted to introduce the concept of using stored procedures to short circuit injection attacks.

IMHO there is never a good reason to use dynamic sql, either as a command or wrapped in a stored procedure. Just create as many stored procedures as you need to handle the different scenarios, and call them appropriately.

Whenever I bring this approach up, development time up front seems to be the biggest pushback. But using this approach not only provides the security and performance benefits you mentioned, but also abstracts the internals of the db layer from the application/business layer. It also makes modifying application code as easy as calling a new sproc, instead of the nightmare of trying to maintain 300 lines of stored procedure code.





Tatsu
Tatsu
Say Hey Kid
Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)

Group: General Forum Members
Points: 696 Visits: 307
I agree completely. In fact, the intent of of mentioning the code generation in my last post was to debunk the development time argument.

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64495 Visits: 19117
good comments and a great article. One other thing that I think should be mentioned first is that the user account should not be SA, but rather a limited rights account. To me that's the first step before attempting to limit injection attacks.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search