php - Is this function vulnerable to SQL injection? -

i built function check if table exists on database using pdo, i'm not sure if i've secured properly.

public function tableexists($table){     try{         $this->query('select 1 `'.str_replace('`', '', $table).'` limit 1');     }catch(\pdoexception $e){         if($e->errorinfo[1] == 1146){             return false;         }         throw $e;     }     return true; } 

is possible attacker compromise query if $table provided directly user input? (extreme case)

no, code not vulnerable sql injection attack

however, perhaps more fluke else.

to handle user-provided values correctly, 1 wish escape characters have special meaning (rather remove them). documented under schema object names:

identifier quote characters can included within identifier if quote identifier. if character included within identifier same used quote identifier itself, need double character. following statement creates table named a`b contains column named c"d:

mysql> create table `a``b` (`c"d` int);

so how can permit any table name, including contain backtick characters?

permissive escaping—attempt 1

you might tempted modify function use following:

$this->query('select 1 `'.str_replace('`', '``', $table).'` limit 1'); 

don't! above code is vulnerable (in obscure edge cases).

str_replace() naive, byte-wise function (it not character-set aware, , therefore not safe use string encodings have multi-byte characters).

if database connection uses multibyte character set, such gbk, malicious table name not escaped properly:

// malicious user-provided value $_post['tablename'] = "\x8c`; drop table users; -- ";  // call function value tableexists($_post['tablename']); 

the above result in query() being called following string argument:

select 1 `宍`; drop table users; -- ` limit 1 

this because when str_replace() goes through inputted string byte-by-byte—naively replacing occurrence of '`' character, i.e. byte 0x60—it without understanding mysql consider string encoded using gbk, in 0x8c60 single character '宍'; , therefore turns such characters 0x8c6060 represent '宍`'. str_replace() has introduced terminating backtick character not there before!

permissive escaping—attempt 2

the problem can fixed using character-set aware replacement function. php doesn't have 1 default, although optional extensions multibyte string commonplace in typical hosting environments.

if following approach, must ensure perform replacement using the character encoding of database connection.

permissive escaping—attempt 3

as of mysql v5.7.6, can use mysql_real_escape_string_quote() escape sql identifiers using database connection's character set. sadly however, pdo api not (yet?) provide interface c function…

what whitelisting?

whitelisting considered more secure , reliable escaping. (unless 1 knows in advance not contain special characters), one must still escape whitelisted values—so doesn't progress general cases, although limit damage can done should escaping prove erroneous.

so what's conclusion?

it's quite difficult use arbitrary, user-provided, sql identifiers in safe way.

fortunately, however, it's not common requirement. general rule, one's schema should both static (absent changes one's codebase necessitate schema modification) and compliant principle of orthogonal design: if both of conditions fulfilled, sql identifiers part of one's static code , there not need use user input in place.