CODEWITHSUNDEEP
mysqlpdoprepared-statementsql-injectioncode-injection
Cito
Cito

Reputation: 1709

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

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.

Upvotes: 0

Views: 145

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157909

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.

Upvotes: 1

Barmar
Barmar

Reputation: 781751

You can use:

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

or just remove all non-alphanumeric characters with:

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

Upvotes: 1

Related Questions