mysql sanitize row name

Question

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

Can you show some code or give specific use cases with what row names you're using? If you can't use prepared statements, you're doing something terribly wrong. — Matt, Feb 15 '13 at 16:21
@edave **STOP**. Do not post code in comments. Update your answer. — Kermit, Feb 15 '13 at 16:35
Out of curiosity, why create an ORM when there are open source ones that actually do their job good? — N.B., Feb 15 '13 at 16:40
This is not the ORM. It is the level below that. The ones that I have seen out there never really fitted what I wanted to do, or they were realy heavy. (lots of code) Also, I think to really understand something you should have tried it yourself. In a real production enviroment, I would of cause fallback to availible ORM. — edave, Feb 15 '13 at 16:50

score 0 · Accepted Answer · edited May 23 '17 at 12:27

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.

It scares me a bit that I can't see any obvious attack against this simple solution. I will implement it. (Whitelisting is already part of the layer above, my ORM, but they can be used seperate) Thank you! — edave, Feb 15 '13 at 19:41

Bill Karwin · Answer 2 · 2022-10-22T00:14:10.497

Because of how the query builder works, i sadly cannot use prepared statements. How can I fix this?

If you can't use query parameters, then change the query builder to apply escaping to its arguments before interpolating them into SQL expressions.

Lots of people correctly advocate for query parameters, but escaping is also safe IF you do it correctly and consistently.

Cf. mysqli::real_escape_string()

Re your comment, okay I see where you're going. I was confused because you said "row name" and that's not the correct terminology. You must mean column name.

Yes, you're right, there are no functions in any of the MySQL APIs to escape table or column identifiers correctly. The escaping functions are for string literals and date literals only.

The best way to protect SQL queries when untrusted input names a table or column is to use allowlisting. That is, test the argument against a list of known table names or column names, which you either code manually, or else discover it from DESCRIBE table.

See examples of allowlisting at my past answers:

escaping column name with PDO
PHP PDO + Prepare Statement
My presentation SQL Injection Myths and Fallacies
My book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

I already use mysqli::real_escape_string(), but i can't see how to apply this to table/row names, since it won't filter the ` char i'm using — edave, Feb 15 '13 at 16:58

mysql sanitize row name

2 Answers2