Doctrine: Pass table name as parameter

Question

I'm using Doctrine on Symfony and need to safely pass a dynamic table name to an SQL query, since the "user" needs to be able to choose what backup table to restore values from.

My problem is that when I pass it as a parameter like in the following code, the table name is put in apostrophes which results in incorrect SQL syntax.

$sql = "UPDATE article a LEFT JOIN :restoretable rt USING (articleid) 
        SET a.stock = rt.stock";
$stmt = $conn->prepare($sql);
$stmt->execute(['restoretable' => $restoretable]);

The old code used sprintf("UPDATE article a LEFT JOIN %s ...", $restoretable) and was prone to SQL injections.

My question therefore: How can I safely pass a table name (or row name, or anything else that can't be escaped with apostrophes) to my query?

Thanks in advance

Answer 1

After researching about the possibilities PDO offers, along with those of Doctrine, I came to the following conclusing: As stated by michal, it is not possible to pass a table name thrugh a Parameter, neither in Doctrine, nor in PDO. Verification of the validity has to be done manually.

Two of the possible solutions are as follows (there are also other solutions, but I'm describing here the ones I'm using now):

The first option is better, since the user input never gets close to the table. However, the table names have to be known at the time of coding. I use this to make a copy of an existing, known table.

switch($userinput) {
    case "art": $tablename="article"; break;
    case "cus": $tablename="customer"; break;
    default: return false;
}

$newtablename = $tablename."_".date("YmdHis");

$conn = $this->em->getConnection();
$stmt = $conn->prepare(sprintf("CREATE TABLE %1\$s LIKE %2\$s; 
                                INSERT %1\$s SELECT * FROM %2\$s",
                                $newtablename,$tablename));
$stmt->execute();

Option two has the advantage that you can work with table names that are not explicitly known. However, the table name should not be completely random, and as such at least we should know the naming format. In my case it's "article_".date("YmdHis").

We can now use a regular expression to check the user input for validity (in this case:

$restoretable = $form->get('restoretable')->getData();
if (preg_match("/^article_[0-9]{14}$/i", $restoretable)) {
    $conn = $this->getDoctrine()->getManager()->getConnection();

    $sql = sprintf("UPDATE article a LEFT JOIN %s rt USING (articleid) 
                        SET a.stock = rt.stock", $restoretable);
    $stmt = $conn->prepare($sql);
    $stmt->execute();
}
else {
    return false;
}

Just be sure to check your regular expression thoroughly.