SRB-2006-0004

Users with any SRB account can elevate their privileges to an SRB administrator. With this privilege, they can read, modify, and delete any data or metadata in the SRB. They can also gain the ability to run code as the user account running the srbServer. Depending on the configuration, they may also be able to gain access to the account running the database management system (DBMS) used by the SRB.

SRB user

This vulnerability requires a user to be able to connect and authenticate to an SRB server from any client machine.

low

To exploit this vulnerability requires only the execution of S-commands (SRB client command line utilities).

high

The user can gain complete control of the SRB by elevating their account type to an SRB administrator. With this privilege, complete control over data in the SRB is obtained, and gaining access to run arbitrary code should not be difficult. Under certain configurations, it may be possible to gain access to run code as the operating system account running the DBMS used by the SRB.

This vulnerability is caused by the architectural design flaw of not properly validating and transforming user-supplied data used to create an SQL statement, resulting in an inherently vulnerable system.

The SRB client utilities and libraries pass numeric and string literal values that are user-supplied to an SRB server. The client libraries also generate SQL fragments based on user-supplied data that are passed to an SRB server. The user-supplied data and SQL fragments are then used to create an SQL statement that is evaluated by a DBMS. It is possible for the user-supplied data to terminate a quoted string early, and the remainder of the string could contain text that is able to affect the result of the SQL statement or contain additional arbitrary SQL statements.

SQL Injection Introduction

An SQL injection occurs when user-supplied data is used to create a string representing an SQL statement, where the user-supplied data changes the intended meaning of the statement. This occurs due to improper validation, where the data is intended to be of a certain form, but due to incorrect validation or transformation, the intended meaning of the SQL statement is changed.

SQL injections in the SRB occur in two forms. The first is user-supplied data that is intended to be used as an SQL string literal; this is a problem of data transformation. The second form is user-supplied data that is intended to used as is, such as a numeric literal value, or an SQL fragment. It is easy to see how the second form can be exploited if not properly validated. How an SQL injection in an SQL string literal can occur will now be described.

String literals in SQL consist of a series of characters delimited by single quotes. If the literal contains a single quote, the single quote is escaped by replacing it with two consecutive single quotes. As an example, the literal, I'm 'fine', is represented by the SQL string literal 'I''m ''fine'''. Some DBMS's further complicate the proper escaping of single quotes by allowing alternative escaping methods.

The user-supplied strings are checked to see if they contain the SQL quote character ('), and an attempt is made to quote or escape the single quote. The problem is that the validation of user-supplied data is done incorrectly for certain values. The code only quotes isolated single quotes, using the SQL standard method of two adjacent single quotes. Isolated quotes are handled correctly. An even number of consecutive single quotes are also not a security concern as they will not cause the quoted string to be unquoted prematurely; although from the user's perspective, the interpretation may be incorrect. The security problem occurs when the user-supplied string contains an odd number of three or more consecutive single quotes. These will be passed to the SQL interpreter untouched, which will interpret pairs of the quotes as one single quote, the final odd quote will prematurely end the quoted string, and the rest of the user string will be interpreted as SQL outside of a string.

An example of how an SQL injection occurs is as follows:

The system contains a template of an SQL statement, such as

    select * from T where s='$V' and i=5
  

where $V is replaced with a user-supplied string, such as

    $V = "''';delete from T --"
  

then the following string is what is executed by the server:

    select * from T where s='''';delete from T --' and i=5
  

The result is that all the rows contained in the database table T will be deleted. The sequence '--' is the SQL begin comment sequence, which will cause all text to the end of the line to be ignored.

Elevating Any SRB Account To Be an SRB Administrator

The difficulty is that certain characters in the user-supplied value are treated specially and must be substituted with other sequences. Obviously, the single quote character is one such example. There are two ways to work around this. Some DBMS's, notably PostgreSQL, allow an alternate quoting sequence using $$ (or $id$, where id is a valid SQL identifier). A more universal method is to create the string constant by concatenating a sequence of characters created with the SQL chr function. If a single quote is needed in the string, the second method or a combination of both methods will need to be used. For example, SQL string literal 'ABC', can be replaced with the equivalent values: $$ABC$$, chr(65)||chr(66)||chr(67), or concat(chr(65),concat(chr(66),chr(67))).

Other values that the SRB may treat specially for certain values are || (this is the SQL concatenation operator), and the characters &, *, and ?. The operator || can be replaced with the function concat, other characters can be replaced with the chr function and concatenation.

Below is a series of Bourne shell statements that will elevate the account type of the username and domain to be an SRB sysadmin. This example makes use of the PostgreSQL alternative quoting feature, so it is specific to a PostgreSQL DBMS, but it could easily be changed as described above to work with other DBMS's. This example uses the Sls command to deliver the SQL injection, but most other S-commands will allow an SQL injection to be delivered to the server. The name of the user and the domain need to be set correctly. The construction of the SQL statement in several Shell statements is performed solely to avoid avoid ambiguous line breaks in this document.

    #!/bin/sh

    # Upgrade a users account to be an SRB administrator, by getting
    # the following SQL statement to run on the SRB's DBMS:
    #
    # update mdas_cd_user
    #   set user_typ_id=$$0001$$
    #   from mdas_au_domn as A, mdas_td_domn as T
    #   where mdas_cd_user.user_id=A.user_id
    #     and user_name=$$myUser$$
    #     and A.domain_id=T.domain_id
    #     and domain_desc=$$myDomain$$

    # Q is PostgreSQL's alternative quote, and the others define
    # the user and domain to upgrade to an admin user type
    #
    Q='$$'
    SRB_USER="${Q}myUser$Q"
    SRB_DOMAIN="${Q}myDomain$Q"
    SRB_ADMIN_TYPE="${Q}0001$Q"

    SQL="''';"
    SQL="$SQL update mdas_cd_user
    SQL="$SQL set user_typ_id=$SRB_ADMIN_TYPE"
    SQL="$SQL from mdas_au_domn as A, mdas_td_domn as T"
    SQL="$SQL where mdas_cd_user.user_id=A.user_id"
    SQL="$SQL and user_name=$SRB_USER
    SQL="$SQL and A.domain_id=T.domain_id"
    SQL="$SQL and domain_desc=$SRB_DOMAIN"
    SQL="$SQL --"

    Sls "$SQL"
  

Once the user has elevated their account type to a sysadmin, they can read, modify, and delete files as described in the vulnerability report SRB-2006-0001 using the Sregister command. If the SRB server executables or configuration are owned by the same operating system user that is used to run the server, then the scripts in bin/commands can be easily modified and then executed using the Spcommand. If not, there are other files in the SRB account's home directory that will cause code to be executed when the account is used by a user. These include .bashrc, .cshrc, .vimrc, and .emacs.

If PostgreSQL is used as the DBMS and the database account is an administrator, then arbitrary operating system files can be read, modified, and created with the same constraints as the DBMS's operating system account. This can be accomplished using the COPY statement. The SRB install.pl script sets up the database account this way.

SQL injection
failure to validate input

This vulnerability is caused by the architectural design flaw of not validating and properly transforming user-supplied data, resulting in an SQL injection.

The first problem is that the code to transform user-supplied data that will be used as an SQL string literal is incorrect. It only escapes isolated single quotes and does not properly deal with multiple adjacent single quotes. It appears that it was done to make the quoting function idempotent, as the same quote escaping code is run on both the client and the server. It is not possible to have an idempotent quote escaping function if all characters codes are valid in the string to be quoted.

The second problem is that other user-supplied data values are not validated. These are values that are used as fragments of SQL or numeric literals, and they are used with little or no change in the construction of SQL statements. For instance, the user-supplied data that forms a query of a set of SRB attributes has a syntax that is very close to SQL. The query string is only minimally transformed to generate the executed SQL statement. What should be numeric and string constants are not validated to be such. This makes an SQL injection easily possible through the use of the fragments, although a few more characters are treated specially (changed in the transformation to other characters) and cannot be used in the user-supplied value, but this is easy to work around using equivalent fragments of SQL without the specially treated characters.

Two changes are required to mitigate this vulnerability. The first change is that the code to transform user-supplied data used as SQL string literals needs to be done correctly. The second change required is that validation checks need to be performed properly on client-supplied data that is used to construct SQL statements.

Proper Transformation of Data Used As SQL String Literals

The current SQL quote escaping algorithm is incorrect. The quote-escaping transformation is done in both the client and the server using the same algorithm. It is not possible to create an idempotent escaping function unless some characters are reserved for this escaping purpose. Since SQL escapes quotes using a character sequence that is also a valid SQL string value, this transformation must be once and only once; in either the client or the server, but not both. If it is done in the client, the server must verify that it was done correctly.

The quote escaping code needs to be changed to handle arbitrary character sequences in the user-supplied data used in a string literal, include single quotes and other characters treated specially by the DBMS parser. This is made more complicated by DBMS's that provide non-standard quote escape methods. PostgreSQL is an example of this, where a single quote with a string literal can be represented by not only '', but also \'. PostgreSQL now has a configuration option to disable this, and it will be the default in a future release. This can cause problems for code performing a standard escape method of doubling single quotes. It does not work for the sequence \', as this becomes \'' and the last single quote is unescaped.

One way to handle all cases is to convert a string AQB, where A and B represent arbitrary strings, and Q is a ' or \, into 'A'||chr(q)||'B', where q is the integer value of the ASCII code for the single quote or backslash. If there is more than one single quote or backslash in the string, the algorithm can be applied recursively.

Another way to handle this would be to check at runtime for \-escaping by executing a series of statements that are valid in either scheme but produce different results. This can be accomplished by having a table with a single text column and then run the following SQL statements: delete from T; insert into T values('\')--'). Then execute select * from T. If the result is \, then the DBMS does not support \-escapes, but if the result is ')--, then the DBMS does support \-escapes. If \-escaping is supported, then besides doubling single quotes, backslashes should also be doubled to properly quote and escape the contents of the string literal.

There should be a function that takes a string as input and returns a properly quoted and escaped string that the SQL interpreter will evaluate as identical to the string passed into the function. This function needs to have knowledge of the DBMS used by the SRB to perform this function properly. For this reason, the quoting of strings should be done in the server.

Strings should be properly escaped and quoted in the server, preferably only right before they are interpolated into an SQL statement. This way, all strings passed around the system are unquoted and unescaped, and there is no danger of multiple quoting/escaping as a proper quoting/escaping function is not idempotent.

In the current server implementation, SQL statement are created using constructs such as

    sprintf(sqlStmt, "select * from t where s='%s'", value);
  

If the values are sent unquoted from the server, and a function existed to create an SQL fragment that returns a string representing the SQL string literal that has the same value, then the following idiom could be used (note the lack of single quotes):

    quotedValue = sql_quote(value);
    sprintf(sqlStmt, "select * from t where s=%s", quotedValue);
    free(quotedValue);
  

A further refinement would be to create a function that mimics printf. It can be simpler in some respects as it only needs to support the format specifiers for integer (%d), float (%f) and string (%s). It would need to add format specifiers to include SQL string literal (%S) and quoted SQL identifier (%I, rarely used double quoting of identifiers to allow nonstandard identifiers). The idiom to use would then be the following (note the lack of single quotes, and there is complete error checking):

    int len;
    len = sql_snprintf(sqlStmt, sqlStmtLen, "select * from t where s=%S", value);
    if (len >= sqlStmtLen)  {
	ERROR_SQL_EXCEEDS_BUFFER_SIZE();
    }  else if (len < 0)  {
	OTHER_ERROR()
    }
  

In this case, the call to sql_quote would be done inside the sql_snprintf function.

Another option, which is dependent on the database and the database client driver support, is to use prepared statements. This allows the SQL statements to be created using ? as a placeholder, thus the template would be select * from t where s=?. This template would be passes to the DBMS, where it would be parsed and have a plan generated. The DBMS would return a handle to the prepared statement. The prepared statement could then be executed by passing the handle and binary values for the placeholders (C int's and char*'s). This technique can only be used when the SQL statement is fixed, except for numeric and string literal values.

Performing Proper Data Validation in the Server

The other mitigation that needs to be performed is that data needs to be properly validated for its use in the SRB and its context in the SQL statement. Data that is used in a numeric context needs to be a valid SQL numeric constant, as do dates and truth values. Data that has a special meaning to the SRB, such as a user or group names should be validated using characteristics of the text before using in an SQL statement: length less than maximum allowed, contains only characters allowed in a user id.

Currently, the client transforms some user input to escape strings containing single quotes and produces SQL fragments. The server performs the same transformation. These are then used to construct an SQL statement in the server. Since performing the correct quote escaping transformation in both the client and the server cannot be done correctly, due to a correct transformation not being idempotent, there are two options for validation. Have the transformations occur only in the server, or have it occur only in the client.

There are trade-offs, in the case where the transformation is performed only in the server, the clients become simpler as this code can be removed from them. The server will then get the value the user intended, which will in most cases greatly simplify the validation that the server must do to the data from the client. Much of the validation in the server then becomes validating that values used as SQL literal constants are proper SQL literals and nothing more. For values used as an SQL string literal, only a proper quote escaping transformation needs to be run. The only downside is that it will break backwards compatibility with older clients.

If backwards compatibility with older clients is required, then the server will need to support the validation of user-supplied data that has been transformed in the client, as it is done now. Since the data needs to be quote-escaped properly in the server due to the quote escaping transformation being DBMS dependent, the client data should be validated that it is properly quote escaped for standard SQL and rejected if not. The data should then have the quote escaping transformation undone; then it should be validated and transformed as if it was not transformed on the client.

The validation in the server needs to be more robust no matter if quote escaping transformation is done in the client or server. Several validation functions should be written for use in the server, including:

1. A function to check a value that has had a quote escaping transformation in the client is a correctly escaped SQL string literal when delimited with single quotes. This function must validate that all quotes are in the form of consecutive pairs. This function may also need to deal with alternative escaping mechanisms present in the DBMS, such as \-escaping of single quotes.
2. Functions to check if a value to be used as an SQL basic type, such as a numeric constant, is of the proper form with no additional characters.
3. Multiple functions to verify that SQL fragments are proper for their intended use. Ideally, values such as those used to query attributes would be stated in a well defined language independent of SQL (it may look like SQL) that is well-defined and can be parsed unambiguously against a grammar with identifiers, operators, and types of literals and their values further validated. The resulting parse tree would then be transformed into the intended SQL fragment. The functions will need to validate all or some of the following properties of the fragment, plus potentially other properties:
   1. does not contain a semicolon outside an SQL string literal that would terminate the current SQL statement
   2. does not contain SQL comment sequences '--', '/*', and '*/' that would eliminate part of the intended SQL statement
   3. the type of literals (string or numeric) are correct for their use
   4. no extraneous clauses exist in a series of conditionals (adding or 1=1 can make everything match)
   5. no extraneous fragments of SQL, such as a UNION clause, that can change the result of the returned set of rows to contain arbitrary values
   If a real parsing of user-supplied queries was performed, it would also have the added benefit that characters that occur inside of a string literal, can contain the character sequences that are treated specially by the SRB such as ||. The current code does a purely textual match looking for SRB metacharacters and does not distinguish if they are in string literal, or if special transformation should only be done on certain string literals. If this parsing were done on the client, characters treated specially there could also be used in string literals.

Limiting Damage from a Successful Exploit

There are several things that can be done to provide security in depth that will limit the damage that a vulnerability of this type can accomplish. The Sregister command should be further restricted; and operationally, multiple operating system account should be used between the SRB and DBMS, and there should be multiple DBMS accounts used by the SRB. These may be done in production systems, but are not documented, nor does the recommended way to install the system, using install.pl, perform these items.

The Sregister should be limited to allowing the registration of files to a set of blessed subdirectories. These should not be changeable through the SRB interface, but should instead require a system administrator to change a configuration file or create a symbolic link within a blessed subdirectory to the new directory tree to Sregister. This would disallow reading, modifying and deleting of files with the same privilege as the SRB server's operating system account. It would also prevent the reading of operating system files that are useful in attack and in replacing SRB and DBMS files that can be used in an attack.

There should also be at least three operating system accounts for use by the SRB and DBMS. With these three accounts, a compromise of the SRB or the DBMS will limit the damage that an attacker can do to the other and will prevent the compromise of files that should be immutable, such as binaries.

1. There should be an account which is used to own file that should be immutable during the operation of the SRB. This includes the executables, scripts, libraries, configuration files and other data files that should be immutable. This account may be the UNIX root account, or another administrative account whose sole purpose is to prevent modification to these files. It may be two accounts: one account to own files related to the the SRB, and another for those related to the SRB. If there is sensitive data that the SRB and/or DBMS software need, which needs to be kept private from others, then a group will need to created to allow them access and to prevent others from reading the data.

2. There should be an account which is used to run the SRB software. This account should own the files in the resource directories where the SRB server places stored data files, and it should also own the log files.

3. The final account should be a similar account which is used to run the DBMS. This account should own the files that need to be modified during the normal operation of the DBMS, such as data and log files.

There should also be at least two separate DBMS accounts, ideally three, so a compromise of the software using the DBMS is limited in the damage that it can cause.

1. There is the DBMS administrator account. This account can create other roles, or accounts, in the DBMS. This account is the root user of the DBMS, they can do anything to the DBMS including removing users and tables, and in some DBMS's, like PostgreSQL, they can cause operating system files to read or write using SQL commands like COPY. Ideally, this account would only be used to create the next user and the database for that user. The install.pl script uses this account exclusively by default.

2. The second account, which could be combined with the administrator account, is an account which owns the tables and indexes used by the SRB. This account should have the ability to create, modify, and delete the database tables used by the SRB. With this account, all the database tables used by the SRB can be managed, but other tables and system tables used by the DBMS cannot be affected to attack other aspects of the file system or DBMS. The SRB administrator could use this account to apply schema modifications to the SRB's DBMS schema.

3. The final account is the database account used by the running SRB server. This account should only have access to the tables used by the SRB; it should not have the ability to modify the schema, indexes, or other metadata in the DBMS. Ideally, each table would be granted only the rights that are required by the DBMS. If the SRB never deletes an entry from a table, this DBMS account should not be granted the privilege to delete privilege. If the SRB only needs to read data in a table, this DBMS account should only be granted the select privilege on the table. If the SRB only needs to append records for an audit trail, the DBMS account should only be granted the insert privilege (and optionally the select privilege if it needs to also read the data). If the SRB DBMS access is restricted to this last account, a compromise of the server can only allow modifications of the DBMS that an SRB server could make, and it could not create additional tables or affect the schema as an aid in an attack.

Almost all database queries were modified to use prepared statements, eliminating the possibility of an SQL injection. The only place where it was not possible to use a prepared statement, or to validate the data to prevent SQL injections, was if the server is built with ALLOW_UDF (allow user defined functions) turned on. There is not a good way around this, so sites that require the use of UDF's need to be aware of the security risk and need to restrict access to only trusted users.

This research funded in part by NATO grant CLG 983049, the National Science Foundation under contract with San Diego Supercomputing Center, and National Science Foundation grants CNS-0627501 and CNS-0716460.