Re: Protection from SQL injection

> As you know, "SQL injection" is the main security problem of databases
> today.
>
> I think I found a solution: 'disabling literals'. Or you may call it
> 'enforcing the use of parameterized statements'. This means that SQL
> statements with embedded user input are rejected at runtime. My
> solution goes beyond saying "developers ~should~ use parameterized
> statements". That is not a solution because developers are lazy. My
> solution is: "developers MUST use parameterized statements". It goes
> like this: Literals are disabled using the SQL statement:

I have found that the little bit of code posted afterwards did eliminate
SQL holes in my PHP applications with zero developer pain, actually it is
MORE convenient to use than randomly pasting strings into queries.

You just call
db_query( "SELECT * FROM table WHERE column1=%s AND column2=%s",
array( $var1, $var2 ));

It is inspired from the Python interface which performs the same (but
slightly more elegantly).
I have removed the logging features for clarity.

function db_quote_query( $sql, $params=false )
{
// if no params, send query raw
if( $params === false ) return $sql;
if( !is_array( $params )) $params = array( $params );

// quote params
foreach( $params as $key => $val )
{
if( is_array( $val )) $params[$key] = implode( ', ', array_map( intval,
$val ));
else $params[$key] =
is_null($val)?'NULL':("'".pg_escape_string($val)."'");;
}
return vsprintf( $sql, $params );
}

function db_query( $sql, $params=false )
{
// it's already a query
if( is_resource( $sql ))
return $sql;

$sql = db_quote_query( $sql, $params );

$r = pg_query( $sql );
if( !$r )
{
echo "<div class=bigerror><b>Erreur PostgreSQL :</b><br
/>".htmlspecialchars(pg_last_error())."<br /><br /><b>Requête</b> :<br
/>".$sql."<br /><br /><b>Traceback </b>:<pre>";
foreach( debug_backtrace() as $t ) xdump( $t );
echo "</pre></div>";
die();
}
return $r;
}

>
> SET ALLOW_LITERALS NONE;
>
> Afterwards, SQL statements with text are not allowed any more for this
> session. That means, SQL statement of the form "SELECT * FROM USERS
> WHERE PASSWORD='qerkllkj'" will fail with the exception 'Literals are
> not allowed, please use parameters'. It is like the database does not
> know what ='qerkllkj' means. Only statements of the secure form are
> allowed, for example "SELECT * FROM USERS WHERE PASSWORD=?". This
> solves the problem because SQL injection is almost impossible if user
> input is not directly embedded in SQL statements.
>
> The 'ALLOW_LITERALS NONE' mode is enabled by the developer itself, or
> by an administrator. It is still possible to generate SQL statements
> dynamically, and use the same APIs as before, as long as SQL
> statements don't include literals. Literals can still be used when
> using query tools, or in applications considered 'safe'. To ease
> converting the application to use parameterized queries, there should
> be a second mode where number literals are allowed: SET ALLOW_LITERALS
> NUMBERS. To allow all literals, execute SET ALLOW_LITERALS ALL (this
> is the default setting).
>
> So far this feature is implemented in my little database H2. More
> information about this feature is described here:
> http://www.h2database.com/html/advanced.html#sql_injection
>
> I know about the Perl taint mode, but this is only for Perl. I also
> know about disabling multi-statement commands (only solves part of the
> problem). PostgreSQL should also support database level 'constants'
> that are similar to constants in other programming languages,
> otherwise application level constants (such as 'active') can't be used
> in queries directly (I propose to add new SQL statements CREATE
> CONSTANT ... VALUE ... and DROP CONSTANT ..., example: CREATE CONSTANT
> STATE_ACTIVE VALUE 'active'). I also know the 'disabling literals'
> feature does not solve SQL injection completely: for example 'ORDER BY
> injection' where an application dynamically adds the column to sort on
> based on a hidden 'sort column' field in a web app. To solve that I
> suggest to support parameterized ORDER BY: ORDER BY ? where ? is an
> integer. Then, instead of using SET ALLOW_LITERALS NONE the use of
> literals should probably be two access right (REVOKE LITERAL_TEXT,
> LITERAL_NUMBER FROM APP_ROLE). Those are details that still need to be
> discussed.
>
> What do you think about it? Do you think it makes sense to implement
> this security feature in PostgreSQL as well? If not why not? Does
> PostgreSQL have another solution or plan to solve the SQL injection
> problem?
>
> Regards,
> Thomas
>
> P.S. I have send this proposal to pgsql-sql(at)postgresql(dot)org first and
> got replies, but I would like to get some feedback from the PostgreSQL
> developers as well.
>