re SQL Injection Attack using MySQL, does this meet baseline requirements? [duplicate]

This question already has answers here: How can I prevent SQL injection in PHP? (27 answers) Closed 2 years ago.

I have a Single Page Application in which the browser does all the logic work. Except for initial loading, the server is pretty much a fancy interface to the database.

The browser sends data dictionary keys, column name / value pairs, and where clauses for SELECT, for example. The server assembles the parts into SQL, executes the queries, and replies. NEW: In a SELECT, for example, the table name and columns pulled are from the data dictionary - the browser supplies the data dictionary key and the SELECT where clause.

This very open environment is very susceptible to SQL Injection attacks. The goal is to prevent damage from said attacks.

Problems to be Overcome

First, as discussed, it is impossible to paramaterize a random SELECT where clause - SELECT cannot use a pre开发者_如何学编程pared statements.

Second, mysqli, the library for paramaterized statements with MySQL, does not support NULL nor MySQL functions, eg, CURRENT_DATE or NOW(), as discussed.

Proposed Solution

First, if SELECT cannot be paramterized, then execute SELECT by a user who has no DML or DDL rights. This will prevent SQL Injection Attacks from changing the database.

Second, write a wrapper function for mysqli that will allow NULL and MySQL functions to be passed as parameters. This will allow parameters to be easily used for all DML.

Third, shadow highly sensitive data where it cannot be seen or touched by normal queries or normal users. This will put sensitive data, such as passwords, out of the range of attacks.

Forth, write a wrapper order to enforce the user / query type relationship. This will ensure SELECT are executed by the select user, for example

The result of that effort is here. The question is, logically, will this approach successfully protect against SQL Injection Attacks?

Non-Answer Answers

I proposed this same question before. Due to my poor job of presentation, received answers and comments ended up focusing on spurious issues instead of addressing the (admittedly) difficult question - does this actually work?

As a reference, here are some of those comments and answers.

Use PDO's prepared statements

First, prepared statements cannot be used for all SELECTs - how does PDO help? Second, mysqli does not accept NULL or MySQL functions - how does PDO help?

Why re-invent the wheel

If you know of an approach that overcomes these problems, I would really really like to know - this is a difficult problem.

no mysql_real_escape_string() in sight

Values should be sanitized before being passed to the database query functions. mysql_real_escape_string() is one of a set of sanitizing functions available, for example, one would use a different sanitizer for dates.

too much work

Please share with me your know of any approach that overcomes these problems - I would really like better insight. That said, from my setting up the whole thing again, following my notes it took between 30 and 45 minutes. No time costs incurred thereafter.

I am happy with mysqli

How are you going to prevent SQL Injection Attacks when you cannot parameterize your SELECT? Do you expect to never use NULL when updating a column? Every man to his own poison, but these are issues I hope to solve - not live with.

@Konerack pointing out limited number of parameters

Right. Changed to code to use eval() (shudder) which solved the problem. Needs security review.

The Question Again

Will this approach protect against SQL Injection Attacks while overcoming the problems of no parameterized SELECT and mysqli's parameter limitations?

---

The answer is simple:

For parameters use PDO or mysql_real_escape_string().

For other dynamic SQL (table names, column names, syntax elements) use whitelisting.

See: How to prevent SQL injection with dynamic tablenames?

You cannot trust the browser to keep within the bounds you set in Javascript. This can be easily manipulated. So you must treat all data from that end as untrusted. Make sure you check all elements in the where clause against a list of allowed keywords.

For this purpose you'd use:

1. Check against whitelist for symbols: =, <>, >, LIKE, NULL, IS.
2. Check against a whitelist for boolean operators: AND, OR, XOR, NOT
3. All values must be properly enclosed in single ' quotes and you will need to feed these through mysql_real_escape_string() to make sure there are no shenanigans.
4. All values not in (1,2) and not enclosed in single quotes are column names, check them against a whitelist of allowed column names.
5. Reject all other input.