SRB-2006-0006

A user with only the ability to connect to the TCP port that the SRB listens to for client connections can affect the metadata stored in the SRB. No SRB account of any type or ticket is required. This may result in a denial of service of the SRB server.

remote user with no SRB account

This vulnerability only requires that an adversary be able to connect to the TCP port that the SRB server is listening to for client connections.

low

To exploit this vulnerability requires only the execution of S-commands (SRB client command line utilities), and the ability to find a host running an SRB server.

high

Since the integrity of the metadata can be affected, a denial of service attack can be accomplished.

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. This vulnerability is similar to the vulnerability report SRB-2006-0005, except the SQL injection that is possible is greatly restricted in length.

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.

Modifying the SRB Metadata with Only Remote Network Access

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.

An SQL injection is possible through the username and domain. The constraint is that each of these values is at most 31 characters of data. A simplified version of the SQL query that is formed and run on the server is the following:

    select * from T where u='$user' and d='$domain'
  

The injection can occur using the following template:

    $user   = "''';$sqlCodePt1/*"
    $domain = "*/$sqlCodePt2--"
  

This results in the following SQL statement being executed:

    select * from T where u='''';$sqlCodePt1/*' and d='*/$sqlCodePt2--'
  

This allows an SQL injection that can be up to 25 characters, a comment, and then another 27 characters. Even with this restriction it would be possible to create an SRB administrator account, at least when using a PostgreSQL DBMS and an SQL administrator account used by the SRB.

Due to a buffer overflow in procStartupMsg of the variable namebuf, the domain is further restricted to 10 characters, 4 of which comment character, leaving only 6 characters of SQL code. The bytes at character 11 and 12 can be overwritten with values that will allow the server to continue without crashing. The overflow also overwrites a part of the stack frame if it is longer than 25 characters that causes the server to crash. So the longest amount of SQL that can be injected is the 25 characters in the username, and 17 characters in the domain. The domain needs to then be of the form */xxxxxx/*yy*/zzzzzzzzz--, where x and z's represent SQL and y represents a platform dependent constant to allow a pointer to point to valid readable memory.

Given the buffer overflow, a total of only 40 character of SQL is available that must be broken up in 25, 6 and 9 character pieces. Although this is not enough to create an SRB administrator account, other damage can be done to the SRB; data and table can be deleted from the DBMS. The injection can be done using the following idiom:

    export srbUser="''';delete from mdas_cd_user--"
    export mdaseDomainName="xxx"
    Sls
  

This will cause all of the data to be delete from the mdas_cd_user table which should prevent all logins and access to the SRB.

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 ticket in this example should be further validated to look like a ticket: 10 characters long, starts with a valid ticket type letter, and contains only certain characters.

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.