I presented a webinar today about SQL Injection, to try to clear up some of the misconceptions that many other blogs and articles have about this security risk. You can register for the webinar even now that I’ve presented it, and you’ll be emailed a link to the recording, which will be available soon.
During my webinar, a number of attendees asked some good questions, and I wasn’t able to answer them all before the hour was up. Here are the questions and my answers.
Tobin C. asked:
Q: Does the use of Parameters (particulary OdbcParameter class in .NET) qualify as an appropriate security mechanism for normal WHERE interpolation? Or should the input be validated before creating a parameter?
Yes, the OdbcParameter class should be safe. The OdbcParameter abstracts query parameters in order to make sure you can use ODBC in a database-agnostic way. Vendor differences in parameter naming are solved by ODBC interpolating parameter values before it sends the query string to be prepared.
This is different from a true parameterized query, which separates the prepare step from the parameter binding step as I showed in my presentation. However, it’s still safe because the interpolation is done in ODBC with escaping code that is mature and thoroughly tested.
PHP’s PDO library does something similar, interpolating parameter values into a query string before prepare. But I’m not so sure the PDO code is as thoroughly tested.
Jonathan C. asked:
Q: Is your book available through any of the digital library subscription services?
You can purchase my book SQL Antipatterns: Avoiding the Pitfalls of Database Programming in digital formats for Kindle, iOS, and PDF at the publisher site: http://pragprog.com/book/bksqla/sql-antipatterns
Jonathan C. also commented:
Q: My preferred variable parameter format:
1 | bug_id IN (?" . str_repeat(', ?', count($params) - 1) . ") |
Good suggestion, Jonathan. Since you can use parameters for only one value at a time, it’s tricky to create IN predicates. But you can add a variable number of parameter placeholders based on the length of the array of values in your application. Of course there are multiple ways of doing that. Here’s an example of how I do it using PHP:
1 2 3 4 | $placeholders = join(",", array_fill(0, count($params), "?")); $sql = "SELECT * FROM Bugs WHERE bug_id IN ($placeholders)"; $stmt = $pdo->prepare($sql); $stmt->execute($params); |
Radu M. asked:
Q: What about INSERT-ing binary data? Do I have to protect myself in this situation? Why and how?
Any interpolation of unsafe content into an SQL string is potentially an injection risk. If you have binary data, you could interpolate the binary bytes, so it might not be clear how to escape input that contains non-printing characters.
The standard API function mysql_real_escape_string() is aware of binary bytes, and will escape appropriately. SQL also has a builtin QUOTE() function so you can do this in SQL expressions instead of in application code. See http://dev.mysql.com/doc/refman/5.5/en/string-literals.html for more information on string literals.
Allan F. asked:
Q: Is it the DBA’s job to do review the codes that might have SQL injection?
Good question! It’s primarily the responsibility of developers to do self-review and peer review of application code for security handling, but the DBA can definitely help and offer insights into SQL and how the injection risks could affect the database. The DBA should be included in the review process, but it’s not her responsibility alone.
Brian P. commented:
Q: If you’re writing Dynamic SQL in your Stored Procedures you have other issues 🙂
I consulted for a customer who uses dynamic SQL in stored procedures extensively. They found that it was awkward because of the limits of the procedure language MySQL offers, and also it moves too much computation load onto their database server. Their MySQL server became CPU-bound, while their multiple PHP application servers were often idle, waiting for the database server. It would make sense to scale out as much work as possible to the app servers.
Having said that, it’s hard to generalize. There are always edge cases where it’s appropriate to use a stored procedure, even one that writes dynamic SQL. And it only takes one SQL injection vulnerability to cause a big problem. Your site is only as secure as its weakest link, which makes coding and testing for security very challenging. Whereas with performance, your site is working well if you’ve optimized the majority of the most frequently-accessed code, even if not every bit of code.
Andrey M. asked:
Q: Could you specify input data strings I should use to find a security bug in my code?
This is a big topic. One simple answer is that you can try embedding a string-termination quote into an input data string, followed by invalid SQL syntax:
1 | http://example.com/page.php?param=string' TEST TEST TEST |
Hopefully, you have written your code to raise errors so you will then see during testing where you have problems with input being interpolated in an unsafe manner. You don’t need to perform a successful breakin to test for this type of security flaw.
The more difficult part is making sure to test for all such cases in your code where SQL statements interpolate external content. Unfortunately, this can be laborious and time-consuming to do code review to find all these cases.
Andrey M. also asked:
Q: Should I delete all unsafe sentences from user input before I store the data in DB?
I assume you mean to remove content that looks like it might contain SQL reserved words? If we did that, how would we store the legitimate name of a Mr. Order, who lives at 123 Union Street?
If you are careful to escape or parameterize to ensure you insert data values safely, and then also assume that data in the database must be protected again if you use query results in a subsequent SQL query, then you should be able to store such strings safely.
Another risk of storing code-like strings in the database is that they may contain illicit JavaScript. This is not an SQL injection risk, but it can cause trouble when you retrieve the string from the database and output it in an HTML presentation. This type of security issue is as common a SQL injection, and you can read more about it here: https://www.owasp.org/index.php/Cross_Site_Scripting_Flaw
I’d like to see some of the folks who attended my SQL Injection webinar when I present the popular Percona Training in Salt Lake City, September 24-27. See https://www.percona.com/training/ for details on our training offerings and the schedule for upcoming events.
Depending on your application, you might be able to use pt-query-digest with the –review option to spot queries that are attempting to do SQL injection.
The –review table contains a column named ‘reviewed’. You can use this table to mark all of your normal SQL query patterns as reviewed, and when new patterns show up, they will be listed in the pt-query-digest report. By examining the new patterns you can see if any queries contain injection patterns.
Most sites have a limited number of query patterns, and those patterns only change during deployments, so you should be able to see “unusual” queries right away which might be injection attacks.
*edit/addendum*
This can also be very useful with –embedded-attributes. If you embed the filename, line and other information (like application username) into your queries you can not only find out exactly what page an attack is coming from, but exactly what site user is generating the attack (assuming the attack is coming from a logged in user).
Nice article but i have written something differenet about sql injections on my blog . check to chttp://toptechy.com/index.php/2012/10/13/know-how-to-find-out-sql-vulnerability-in-your-website