Preventing SQL Injection at the Database Level

Apr 14, 2021 by Robert Gravelle

Many organizations make some effort to protect their data by implementing input validation within their applications. As valuable as that is, it should be noted that many cyber attacks are aimed squarely at the database servers themselves, where application security does not come into play at all! As a Database administrator (DBA) or Database Developer, you have tremendous power to reduce the risk of cyber attacks, and/or damage that may occur as a result, including from the most common form of cyber attack: SQL Injection. In today’s blog, we’ll explore a few practices that can greatly reduce exposure to SQL Injection attacks.

The more easily a malicious entity can pass in unfiltered SQL to the database server(s), the more susceptible your data will be to loss or theft. By placing all of your queries and data manipulation statements (DML) inside of stored procedures, you can make it much more difficult for hackers to issue DML statements.

The following code example uses a CallableStatement, Java’s implementation of the stored procedure interface, to execute the same database query:

String custname = request.getParameter("customerName");
try {
  CallableStatement cs = connection.prepareCall("{call sp_getCustomerAccount(?)}");
  cs.setString(1, custname);
  ResultSet results = cs.executeQuery();
  // ...result set handling
} catch (SQLException se) {
  // ...logging and error handling
}

User supplied values are not the place to bind database entities such table, column names, or even the sort order indicator (ASC or DESC). Those values should come from your own SQL code, and not from user parameters. To target specific table and column names, parameter values should be mapped to the legal – i.e. expected – table and/or column names to prevent unvalidated user input ending up in the query.

Here is an example of table name validation:

String tableName;
switch(PARAM):
  case "Value1": tableName = "clientTable";
                 break;
  case "Value2": tableName = "employeeTable";
                 break;
  ...
  default      : throw new InputValidationException("unexpected value provided"
                                                  + " for table name");

For something simple like a sort order, one solution is to accept the user supplied input as a boolean, which is then utilized to select the safe value to append to the query. In fact, this is a common practice in dynamic query construction.

public String myMethod(boolean sortOrder) {
  String SQLquery = "some SQL ... order by Salary " + (sortOrder ? "ASC" : "DESC");
  ...

Only when none of the above are feasible should user input escaping be employed. The reason that this defense is considered to be frail compared to other defenses is because there is no guarantee that it will prevent all SQL Injection across every possible situation.

It’s vitally important that you match the user input escaping to your particular database type as every DBMS supports one or more character escaping schemes specific to certain kinds of queries. By escaping all user supplied input using the proper escaping scheme for the specific database you are using, the DBMS will not confuse that input with SQL code written by the developer, thus avoiding virtually any possible SQL injection vulnerabilities.

The OWASP Enterprise Security API (ESAPI) is a free, open source, web application security control library that makes it easier for programmers to harden their applications against cyber attacks. The ESAPI libraries are designed to make it easy for programmers to retrofit security into existing applications as well.

To use an ESAPI database codec is pretty simple. An Oracle example looks something like:

ESAPI.encoder().encodeForSQL( new OracleCodec(), queryparam );

As a Database administrator (DBA) or Database Developer, you have tremendous power to reduce the risk of cyber attacks, and/or damage that may occur as a result thereof, including from the most common form of cyber attack: SQL Injection. By following the practices outlines here today, you can greatly reduce exposure to SQL Injection attacks.

---

Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial organizations. You can hire Rob by emailing him at rgconsulting(AT)robgravelle(DOT)com. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.