[TL:DR] The theft of hundreds of millions of user IDs, passwords, and email addresses was made possible by a database programming technique called dynamic SQL, which makes it easy for hackers to use SQL injection to gain unfettered access to database records. To make matters worse, the dynamic SQL vulnerability can be avoided by using one of several simple programming alternatives.
How is it possible for a simple hacking method which has been publicized for as many as 10 years to be used by Russian cybercriminals to amass a database of more than a billion stolen user IDs and passwords? Actually, the total take by the hackers in the SQL injection attacks revealed earlier this month by Hold Security was 1.2 billion IDs and passwords, along with 500 million email addresses, according to an article written by Nicole Perlroth and David Gelles in the August 5, 2014, New York Times.
Massive data breaches suffered by organizations of all sizes in recent years can be traced to a single easily preventable source, according to security experts. In an interview with IT World Canada’s Howard Solomon, security researcher Johannes Ullrich of the SANS Institute blames an outdated SQL programming technique that continues to be used by some database developers. The shocker is that blocking such malware attacks is as easy as using two or three lines of code in place of one. Yes, according to Ullrich, it’s that simple.
The source of the vulnerability is dynamic SQL, which allows developers to create dynamic database queries that include user-supplied data. The Open Web Application Security Project (OWASP) identifies SQL, OS, LDAP, and other injection flaws as the number one application security risk facing developers. An injection involves untrusted data being sent to an interpreter as part of a command or query. The attacker’s data fools the interpreter into executing commands or accessing data without authentication.
According to OWASP, injections are easy for hackers to implement, difficult to discover via testing (but not by examining code), and potentially severely damaging to businesses.
The OWASP SQL Injection Prevention Cheat Sheet provides a primer on SQL injection and includes examples of unsafe and safe string queries in Java, C# .NET, and other languages.
An example of an unsafe Java string query (top) and a safe Java PreparedStatement (bottom).
Dynamic SQL lets comments be embedded in a SQL statement by setting them off with hyphens. It also lets multiple SQL statements to be strung together, executed in a batch, and used to query metadata from a standard set of system tables, according to Solomon.
Three simple programming approaches to SQL-injection prevention
OWASP describes three techniques that prevent SQL injection attacks. The first is use of prepared statements, which are also referred to as parameterized queries. Developers must first define all the SQL code, and then pass each parameter to the query separately, according to the OWASP’s prevention cheat sheet. The database is thus able to distinguish code from data regardless of the user input supplied. A would-be attacker is blocked from changing the original intent of the query by inserting their own SQL commands.
The second prevention method is to use stored procedures. As with prepared statements, developers first define the SQL code and then pass in the parameters separately. Unlike prepared statements, stored procedures are defined and stored in the database itself, and subsequently called from the application. The only caveat to this prevention approach is that the procedures must not contain dynamic SQL, or if it can’t be avoided, then input validation or another technique must be employed to ensure no SQL code can be injected into a dynamically created query.
The last of the three SQL-injection defenses described by OWASP is to escape all user-supplied input. This method is appropriate only when neither prepared statements nor stored procedures can be used, whether because doing so would break the application or render its performance unacceptable. Also, escaping all user-supplied input doesn’t guarantee your application won’t be vulnerable to a SQL injection attack. That’s why OWASP recommends it only as a cost-effective way to retrofit legacy code.
All databases support one or more character escaping schemes for various types of queries. You could use an appropriate escaping scheme to escape all user-supplied input. This prevents the database from mistaking the user-supplied input for the developer’s SQL code, which in turn blocks any SQL injection attempt.
The belt-and-suspenders approach to SQL-injection prevention
Rather than relying on only one layer of defense against a SQL injection attack, OWASP recommends a layered approach via reduced privileges and white list input validation. By minimizing the privileges assigned to each database account in the environment, DBAs can reduce the potential damage incurred by a successful SQL injection breach. Read-only accounts should be granted access only to those portions of database tables they require by creating a specific view for that specific level of access. Database accounts rarely need create or delete access, for example. Likewise, you can restrict the stored procedures certain accounts can execute. Most importantly, according to OWASP, minimize the privileges of the operating system account the database runs under. MySQL and other popular database systems are set with system or root privileges by default, which likely grants more privileges than the account requires.
Adopting the database-as-a-service model limits vulnerability
Organizations of all sizes are moving their databases to the cloud and relying on services such as Morpheus to ensure safe, efficient, scalable, and affordable management of their data assets. Morpheus supports MongoDB, MySQL, Redis, ElasticSearch, and other DB engines. The service’s real-time monitoring lets you analyze and optimize the performance of database applications.
In addition to 24/7 monitoring of your databases, Morpheus provides automatic backup, restoration, and archiving of your data, which you can access securely via a VPN connection. The databases are stored on Morpheus’s solid-state drives for peak performance and reliability.
