Skip to content
Advertisement

mysql sanitize row name

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.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement