sql injection - how to sanitize program generated sql clause?

Question

in standard Ajax, where and order by SQL clauses are provided by the program (not user), eg

var url = ".select?dd=emp&where="+escape("emp_tp='abc' and hire_dt<current_date-'2 years' and super_emp_id is distinct from emp_id")

answered on the server by

$where = (isset($_GET['where'])) ? pureClause($_GET['where']) : null;
$order = (isset($_GET['order'])) ? pureClause($_GET['order']) : null;
...
$query = $query.(($where)?" where $where":'').(($order)?" order by $order":'');

the question is what should function pureClause look like?

right now pureClause simply raises error if any of the following exist:

; select insert update delete drop create truncate

if other injection causes query failure, that's fine, as long as data undamaged.

to me this seems adequate, but in my heart, I know I'm wrong.

Clarifications:

• prepared statements in Postgres, although very fast, are a pain to set up and maintain - they're ok for well used queries but not custom queries.
• creating a prepared statement for each transaction is a huge db hit. much preferred if security can be attained in at the app level.

Lastly, consider the where clause

emp_tp='abc' and hire_dt=current_dt-'2 years' and super_emp_id is distinct from emp_id

how many placeholders here? this needs to be parsed correctly before being fed into a prepared statement with placeholders, right? or am I completely missing the boat?

---

Primary facts:

• not practical to write a SQL clause parser for parameterized prepared statements
• not practical to write a SQL clause sanitizer that guarantees no harm

Solution:

for SELECTS, where the random SQL can be a problem: since it's too hard to protect the database, let the database protect itself! have different users have different roles / permissions. use a read-only user for selects. for normal SQL, this guarantees no DML from these statements.

best practices: four db user accesses

1. developer, do everything (never use as connection in web app)
2. dml - can select / dml on almost everything (must use for dml)
3. read - can select (use for all selects, whether prepared or text)
4. login - can only execute login/password functions (used in login process)

password protection:

• dml and read may not access password data, either through select or dml
• login should access password data only through protected functions, eg,

     function login( username, password ) - returns user_id
     function set_password( usr_id, password ) - sets password

• only login may run the login() and set_password() functions
• depending on your database, login may need sql access to password columns
• depending on your database, the password column may be protected itself; if not, then should be moved out of the user table into its own secure table

setting this up in mysql, using the administrator tool, took about 30 minutes, including time to write the login functions and split out the password column.

Answer 1

What you are doing is the very definition of sql injection and cannot be sanitized. You cannot pass in a WHERE clause in a safe fashion period end of story. You must build this part of the query on the server side. The fact that you didn't recognize this means you MUST read more about sql injection, clearly asking StackOverflow is an insecure approach to this problem. The fear is that you may never learn the fundamentals of this vulnerability.

$order can be done in a secure way with a white list. For example:

if(in_array($_GET['order'],$list_of_rows)){
   $order=$_GET['order'];
}

If you are passing in a table name or column name make sure you check it against a white list, or this will be sql injection.

Answer 2

Got it! Routing all of these queries through a database user (connection) who has only been granted SELECT privileges on the database!

Attempted DML will choke. This does not prevent DoS attacks (lots of ways to do that!), but does protect the data. Nor does the make for secure queries, like login. But for client generated WHERE and ORDER, with the goal of preventing DML, this should work just fine.

Ten/fifteen years ago always set up different users for different roles, but with app layer etc etc got out of the habit. It's probably a good idea to re-invest in those principles.

Unless hear differently will mark this as correct answer - it satisfies all criteria, howbeit it dodges the theoretically impossible challenge fo writing a sanitizer.

Answer 3

Always use prepared statements. It will handle input escaping and avoid sql injection. There will be no need for hacks like pureClause. Check out mysqli_stmt::prepare()

Answer 4

As @Stephen suggested, provide WHERE as an object, then parse the object and generate safe SQL var where = { emp_tp: { condition: equal value: 'abc' } var order = { emp_tp: 'ASC' }

send it as json:

var params = {
  w: where,
  o: order
}
$.post(url,params,function(result){...}, 'json');

And in PHP

$where = isset($_POST['w']) ? json_decode($_POST['w') : array();
if (!empty($where)) {
  foreach ($where as $field => $data) {
     // validate that field exists
     // validate that operator is valid
     $sql .= sprintf('%s %s "%s"', $field, $data->operator, mysql_escape_string($data->value));
  }
}