It's been a trend for me over the last while to base posts around security issues. Writing secure SQL is really more reliant on the input sanitizing processes (I have posted more details for Classic ASP and PHP input sanitizing) before the SQL is ever executed, as well as the type of database you are working with and the configuration of the server itself. However, your SQL itself can make a difference as well.
This will, by no means be a complete guide to SQL security, but I will cover some basic concepts and hopefully, scare you into caring about security. if you have never had a site that has been hacked you are likely in the "what are the chances" school of thought. If you have been hacked, well, your emphasis on the importance of this subject will grow exponentially.
SQL Injection
One of the most common threats in this regard is SQL Injection. SQL Injection is simply the act of finding user input, such as querystrings or form values, that are not sanitized before being executed, and inserting a SQL command within a hard coded SQL command.
Look at this harmless little query...
SELECT fieldlist FROM table WHERE field = '[somevalue]'
If the input that [somevalue] comes from (say a form field) isn't validated and cleansed it could easily become...
SELECT fieldlist FROM table WHERE field = 'whatever' OR 'x'='x'
...and return records from the database...
Or, even more damaging:
SELECT fieldlist FROM table WHERE field = 'whatever'; DROP TABLE table
...and drop your whole database table.
The hacker could in theory update, insert, drop or truncate your database. Perhaps get a list of all your tables, then start looking through tables.
This is precisely why data cleansing is so important. To know what you are getting is what you are expecting, and in the case of free-form text, you escape the dangerous characters.
Stored Procedures
If the database you are using allows stored procedures, these can help as well. Stored procedures have each bit of data being given to it declared by datatype, and if some data does not match that datatype it errors out and won't process. This is a good additional level of security, as well as a performance booster.
Tips and Tricks
Some of the habits I have gotten in to to tweak code to make it a bit safer is minimal, but effective. I always, whether character, integer or date field, I always surround values with a single tick ('), even though integer don't need it, it does delimit the value area to make a subtle break in the SQL that could thwart a couple amateur SQL Injection attempts.
SQL Injection
One of the most common threats in this regard is SQL Injection. SQL Injection is simply the act of finding user input, such as querystrings or form values, that are not sanitized before being executed, and inserting a SQL command within a hard coded SQL command.
Look at this harmless little query...
SELECT fieldlist FROM table WHERE field = '[somevalue]'
If the input that [somevalue] comes from (say a form field) isn't validated and cleansed it could easily become...
SELECT fieldlist FROM table WHERE field = 'whatever' OR 'x'='x'
...and return records from the database...
Or, even more damaging:
SELECT fieldlist FROM table WHERE field = 'whatever'; DROP TABLE table
...and drop your whole database table.
The hacker could in theory update, insert, drop or truncate your database. Perhaps get a list of all your tables, then start looking through tables.
This is precisely why data cleansing is so important. To know what you are getting is what you are expecting, and in the case of free-form text, you escape the dangerous characters.
Stored Procedures
If the database you are using allows stored procedures, these can help as well. Stored procedures have each bit of data being given to it declared by datatype, and if some data does not match that datatype it errors out and won't process. This is a good additional level of security, as well as a performance booster.
Tips and Tricks
Some of the habits I have gotten in to to tweak code to make it a bit safer is minimal, but effective. I always, whether character, integer or date field, I always surround values with a single tick ('), even though integer don't need it, it does delimit the value area to make a subtle break in the SQL that could thwart a couple amateur SQL Injection attempts.
Secondly, whenever possible, which is often, I really, really try to keep WHERE clauses to comparing on integers, as they are very, very easy to validate and cleanse. Short of that, when it has to be character queries, such as a password, you can do things my use an encryption that will assure the string will have no spaces and be only alphanumeric characters. This, like integers, make it quite easy to cleanse for malicious attacks.
0 comments:
Post a Comment