It is pretty easy to hack a website and/or database server if these conditions are true:
- The application accesses the database using the security context of the administrator or root account. (frighteningly common)
- The site uses dynamic SQL and builds up its query using web form input, as in:
var = "IF EXISTS (SELECT * from users WHERE username='" + #form.usernam# + "' AND password='" + #form.password# + "')" EXECUTE var // if query succeeds, let this person into your website
Exact code above will vary depending on your choice of webserver language and database, but the concept is the same. There are a few problems with this SQL code.
First and foremost, the fact that it is dynamic, and just blithely concatenates user input and force feeds it to the db server. But what if I’m an evil user and entered this in the username field:
' OR 1=1; —
The leading apostrophe ‘ finishes the first half of the where clause as username=”, which shouldn’t return any rows, but the OR 1=1 followed by the semicolon (execute this code now) followed by the comment sequence — (two dashes) ensures that the rest of the dynamic SQL is ignored. Result: all rows are returned and the query succeeds.
An even simpler way to break in to the above site is to just enter a single SQL wildcard character % in both the username and password fields. This guarantees that the dynamic sql will return all rows from the users table, thus passing the IF EXISTS test.
Which brings me to my second point, namely that IF EXISTS is a poor choice for this application because it tests for one or more rows. Valid login credentials should yield one and only one row, so you’d want set a variable, say rows, to the number of rows returned by the query and test something like this:
if rows == 0 echo "Invalid login, try again" elseif rows == 1 // let them enter your site elseif rows > 1 echo 'Login failed." // alert a sysadmin, log the user's IP address, etc endif
Now this is not even the worst that can happen. Expanding on our evil form input example, there’s nothing stopping this hacker from entering this in the username field:
' OR 1=1; DO SOMETHING EVIL LIKE SHELLING OUT TO THE DB SERVER'S COMMAND LINE AND DELETING STUFF; —
Best practices to mitigate risk of SQL Injection attacks:
- Give the anonymous web server account the bare minimum permissions necessary to run your app and serve pages.
- Likewise give this account the bare minimum permissions on the database it needs to connect to: only those tables, columns, views and stored procedures that it needs, and consider which specific access is appropriate (SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP etc) and use GRANT and REVOKE statements as needed to minimize your attack surface.
- Never use dynamic SQL. Use stored procedures whenever processing form fields that a user entered and just pass the form values as arguments to the stored procedure. Even if the hacker tries to pass it malicious SQL code the stored procedure will process each field as dumb text. This is especially important when dealing with real world names in form fields like O’Reilly or d’Amato..
- For read-only data display, consider using a VIEW with only those columns the web app needs to see. This creates even finer grained access control but also adds a useful layer of abstraction: if your business rules change you just redefine the view in the db and *voila* instant rule change without having to hunt for all the app code that references the table to modify the WHERE clause of each because the VIEW in the db is where the WHERE clause lives. So your app code just needs to send something like SELECT * FROM ListActiveUsers and in the db you’d create your view like this:
CREATE VIEW ListActiveUsers AS SELECT * FROM users ORDER BY lastname, firstname
- And now let’s say your site goes from a free to a subscription model and every user account has an expiration associated with it. You just redefine the view as follows and boom you’re done:
CREATE VIEW ListActiveUsers AS SELECT * FROM users WHERE expdate > getdate() ORDER BY lastname, firstname
- If your web app/platform allows it, turn OFF verbose error messages except for specific client IP addresses on your LAN, presumably the IPs of developers, dba’s and sysadmims. Everyone else should get “pretty”, innocuous error messages without any stack dumps or SQL error details, as this information is very useful to hackers trying to break into your site. SQL injection attacks are often trial-and-error affairs where the hacker deliberately injects code that will throw errors to gain as much intel on your db server and app server as possible.