How to sanitize programmed SQL code?

Question

in standard Ajax, where and order by SQL statements are provided by a program (not a user), for example

 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

 $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 does the pureClause function look like?

pureClause right now just causes an error if one of the following exists:

 ; select insert update delete drop create truncate 

if another injection fails the request, this penalty is until the data is corrupted .

it seems adequate to me, but in my heart I know that I am mistaken.

Explanations:

• Postgres pre-made statements, although very fast, are a pain to configure and maintain - they are suitable for well-used queries, but not for custom queries.
• creating a prepared statement for each transaction is a huge blow to db. it is much preferable if security can be achieved at the application level.

Finally, 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 are here? this needs to be properly analyzed before serving in a prepared statement with placeholders, right? or am i completely missing the boat?

---

Key facts:

• Unable to write SQL parser for parameterized prepared statements
• it is impractical to write an SQL class sanitizer that does not guarantee harm

Decision:

for SELECTS, where random SQL can be a problem: since it is too heavy to protect the database, let the database protect itself! different users have different roles / permissions. use for read-only access of the user. for regular SQL, this does not guarantee the DML of these statements.

best practices: four db access users

• developer , do everything (never use as a connection in a web application)
• dml - can choose / dml for almost everything (needs to be used for dml)
• read - can choose (use for all selected, ready or text)
• login - can only perform login / password functions (used during the login process)

password protection:

• dml and read may not access the password data, either with select or dml
• login should only access password data through secure functions, for example,

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

• only login can do login() and set_password()
• depending on your login database, sql access to password columns may be required
• depending on your database, the password column may be protected by itself; if not, it should be deduced from the user table in its own safe table

setting this parameter in mysql using the admin tool takes about 30 minutes, including the time to enter the login functions and split the password column.

Answer 1

Got! Routing all these queries through a database user (connection) that was granted only SELECT privileges in the database!

The DML attempt suffocates. This does not prevent DoS attacks (many ways to do this!), But protects data. In addition, it does not do for secure requests, for example, to log in. But for the client created by WHERE and ORDER, in order to prevent DML, this should work fine.

Ten / fifteen years ago, we always configured different users for different roles, but with the application level, etc. etc. went out of habit. It is probably a good idea to invest in these principles.

If you do not hear in different ways, this will be the correct answer - it satisfies all the criteria, but it evades the theoretically impossible call for writing a disinfectant.

Answer 2

What you do is the very definition of SQL injection and cannot be sanitized. You cannot pass the WHERE at the safe end of the story . You must create this part of the request on the server side. The fact that you did not recognize this means that you MUST read more about SQL injection, clearly asking that StackOverflow is an unsafe approach to this problem. The fear is that you can never learn the basics of this vulnerability.

$order can be done in a safe way using the whitelist. For instance:

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

If you pass the name of a table or column, make sure you check it for a whitelist, or it will be an SQL injection.

Answer 3

Always use prepared statements. It will handle input screens and avoid sql input. There will be no need for hacks such as pureClause . Check mysqli_stmt::prepare()

Answer 4

As @Stephen suggested, provide WHERE as an object, then parse the object and create safe SQL var where = {emp_tp: {condition: equal to 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)); } }