I’m currently writing a php framework with focus on security. I use a query builder to generate SQL-statements, so that it is not bound to MySQL. (Or SQL in general) I found certain posibilities that user could inject row names, so it has to escape them somehow. Because of how the query builder works, i sadly cannot use prepared statements. How can I fix this?
EDIT:
The system works for example like this: db::select()-from('Tablename')->that('rowname')->run()
. And I’m afraid one user could do something like that($_GET['foo'])
or something. I could live with that, but I thought there has to be a way to sanatize this
Advertisement
Answer
To escape backtick you have to double it. Here is a function from my class
private function escapeIdent($value) { if ($value) { return "`".str_replace("`","``",$value)."`"; } else { $this->error("Empty value for identifier (?n) placeholder"); } } //example: $db->query("UPDATE users SET ?u=?s", $_POST['field'], $_POST['value']);
So, it will create a syntactically correct identifier.
But it is always better to whitelist it, as there can be a field, though with correct name,to which a user have no access rights. (So, schema-based solution is still dangerous from this point of view. Imagine there is a role
field with value admin
for the query from my example)
I have 2 functions in my class for this purpose, both accepts an array of allowed values.