Avoid SQL Injection in query using dynamically loaded tables and database names

Question

I'm developing a system to manage in a very simple way some tables in the database.

The system first loads with Ajax the databases the user can see and manage. Then load the tables in that database and then load the data for that table.

I have something like this:

$.ajax({
    url : "myUrl.php",
    data : {
        db : $dbSelector.val(),
        table : tableToLoad
    },
    success : function (json) { /* Some cool stuff here */ }
});

And I've found you cannot use parameterized queries when the parameters are the db name, tables or columns, so I cannot do:

<?php
$query = "SELECT * FROM :db.:table";
$st = $pdo->prepare($query);
$st->execute(
    array(
        "db"=>$db, 
        "table" => $table
    )
);
$rows = $st->fetchAll(PDO::FETCH_OBJ);

I cannot use mysql_ or mysqli_ filtering cause we don't have it installed.

Is it possible to limit db and table names to `\w` character range? Unfortunately, PDO does not have `quoteIdentifier` method. — raina77ow, May 08 '13 at 16:43
@Barmar nope... it will return: 'db'.'table' and that creates an error in mysql: "You have an error in your SQL syntax..." error 42000. raina77ow, what do you mean? — Cito, May 08 '13 at 16:47
As mentioned in one of the comments in the `PDO::quote` documentation, the workaround is to remove the first and last characters after calling it: `substr($dbh->quote($value), 1, -1)` — Barmar, May 08 '13 at 16:50
@Barmar. Is that really the only way of accomplish my request? I mean... I can write a function in PHP to do it (remove everything I know can be used in an injection) but it is not the best practice. Remove first and last character also does not seems quite the best practice to do it... (don't missunderstand me, I appreciate a lot your help :] ) — Cito, May 08 '13 at 16:54
AFAIK, this is it. There's no built-in mechanism to parametrize or sanitize these elements of queries. — Barmar, May 08 '13 at 16:56
@Barmar, then... I think I'll use it since I've not find anything else :( Could you please add what you comment as an answer? So I can choose it as such :) — Cito, May 08 '13 at 18:13
Congrats. You just accepted an answer that made your queries as open to injection as a Pacific ocean. — Your Common Sense, May 08 '13 at 21:54

score 1 · Accepted Answer · answered May 08 '13 at 18:16

1

You can use:

$db = substr($dbh->quote($db), 1, -1);

or just remove all non-alphanumeric characters with:

$db = preg_replace('/\W/', '', $db);

answered May 08 '13 at 18:16

Barmar

741,623
53
500
612

Are you serious with this substr? – Your Common Sense May 08 '13 at 21:52

score 1 · Answer 2 · answered May 08 '13 at 23:11

1

Accepted answer, as well as manual comment it refers to, is a fatal mistake.

PDO::quote has nothing to do with identifiers and shouldn't be used with them at all.
Removing quotes from its output makes it actually WIDE OPEN to SQL injection.

The very point of ingenious PDO::quote() function is to produce a correct string literal. Which means to quote a string and to escape the very quotes inside, if any. Unlike mysql_real_escape_string which does only partial formatting, this is the only proper way of handling strings.
And depriving this function from one of its duties will actually result in a plain injection.

PDO::quote() should never be used to format identifiers. They require totally different formatting.

answered May 08 '13 at 23:11

Your Common Sense

156,878
40
214
345

I know it is not the best solution (from my comments you can see it) but it was the only think I found work. I understand what you mean, but what I don't understand is how to use it... could you explain a little more? – Cito May 09 '13 at 18:41
http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15991422 – Your Common Sense May 09 '13 at 18:46
Really? Maybe you missunderstood my question: I have a dinamically site to admin database. How will I have an array of possible values? It will mean a lot of work for the system: get the databases/tables/fields in MySQL, then check against the things the user send... – Cito May 09 '13 at 18:51

Avoid SQL Injection in query using dynamically loaded tables and database names

2 Answers2