The vast majority of databases in use today have some form of web
interface, allowing internal and/or external users easy access through
familiar browser software. If you're security-conscious, you've
undoubtedly spent a significant amount of time setting appropriate
security permissions on your databases and web servers. Mot much of the DBA's considered the security of the code that powers the database-web
interface.
One common type of database attack, the SQL Injection, allows a
malicious individual to execute arbitrary SQL code on your server. If
you don't believe this type of attack could happen to you.Let's take a look at how it works by analyzing a very simple web
application that processes customer orders. Suppose Acme Widgets has a
simple page for existing customers where they simply enter their
customer number to retrieve all of their current order information. The
page itself might be a basic HTML form that contains a textbox called
CustomerNumber and a submit button. When the form is submitted, the
following SQL query is executed:
SELECT * FROM Orders
WHERE CustomerNumber = CustomerNumber
GO
The results of this query are then displayed on the results page.
During a normal customer inquiry, this form works quite well. Suppose
John visits the page and enters his customer ID (14). The following
query would retrieve his results:
SELECT *
FROM Orders
WHERE CustomerNumber = 14
WHERE CustomerNumber = 14
GO
However, the same code can be a dangerous weapon in the hands of a malicious user. Imagine that Mal comes along and enters the following data in the CustomerNumber field: “14; DROP TABLE Orders”. This would cause the following query to execute:
SELECT * FROM Orders
WHERE CustomerNumber = 14;
However, the same code can be a dangerous weapon in the hands of a malicious user. Imagine that Mal comes along and enters the following data in the CustomerNumber field: “14; DROP TABLE Orders”. This would cause the following query to execute:
SELECT * FROM Orders
WHERE CustomerNumber = 14;
DROP TABLE Orders
GO
Obviously, this is not a good thing! There are several steps that you
can take to protect your server against SQL Injection attacks:
- Implement parameter checking on all applications. For example, if you’re asking someone to enter a customer number, make sure the input is numeric before executing the query. You may wish to go a step further and perform additional checks to ensure the customer number is the proper length, valid, etc.
- Limit the permissions of the account that executes SQL queries. The rule of least privilege applies. If the account used to execute the query doesn’t have permission to drop tables, the table dropping will not succeed!
- Use stored procedures (or similar techniques) to prevent users from directly interacting with SQL code.
As with many security principles, an ounce of prevention is worth a
pound of cure. Take the time to verify the code running on your servers
before disaster strikes! Elsewhere on the site
