ctype_alnum against SQL injection

Question

Prepared statements do not allow parameterised table names. In order to make sure no code can be inserted I wanted to use ctype_alnum to validate all table names (after removing underscores) that come into the database module to shield the application from errors in other parts.

function insert($table) {
    if(!ctype_alnum(str_replace("_", "", $table)))
        throw new Exception("Invalid table name");
    $sql = "INSERT INTO $table VALUES value=:value";
    #... prepare and execute
}

Are there attacks which this would not suffice against? I was thinking about for example multibyte character exploits

Answer 1

I don't know any attacks that will work in this case but I won't do it the way you did because $table can also contain table that might not exist.

You should have whitelist of $tables that you accept so I would do it rather this way:

function insert($table)
{
    $table = trim($table);

    if (!ctype_alnum(str_replace("_", "", $table))
        || !in_array($table, $this->tables)
    ) {
        throw new Exception("Invalid table name");
    }


    $sql = "INSERT INTO $table VALUES value=:value";

    echo $sql;
    #... prepare and execute
}

It will ensure that your code will be safe and if someone misspell table name you won't even try execute the code. In fact you can in this case remove ctype_alnum from condition as you check if value exists in whitelist array.