Php Mysql PDO prevent sql injection while creating table

Question

I have to create table in mysql dynamically. Column names will be provided from client at runtime. And it is also said that don't add user input directly into query.

create table test(dynamic_colname1 string, dynamic_colname2 int......)

So what will be the syntax?

There is a question asked in stackoverflow Using PDO to CREATE TABLE, but in that question columns are hard coded, in my case they are not.

I would use something like Redbean which will safely create it for ya. — Lawrence Cherone
– Lawrence Cherone, Commented Mar 8, 2018 at 20:38
Redbean? That some port of Propel to PHP or something? Or coffee themed names not just Java now :) — ficuscr
– ficuscr, Commented Mar 8, 2018 at 20:39
Was confusing it with Hibernate... And no longer making sense — ficuscr
– ficuscr, Commented Mar 8, 2018 at 20:44
Also note, dynamically creating tables at runtime based on client input is a code smell. The legitimate need to do that is quite rare. — Alex Howansky
– Alex Howansky, Commented Mar 8, 2018 at 20:47

Bill Karwin · Accepted Answer · 2018-03-09 23:30:42Z

You can't concatenate user input (or any other untrusted source) directly into an SQL query safely. That is the definition of an SQL injection vulnerability.

You also can't use query parameters for column names or other syntax.

But you can try to filter the untrusted input to make it trusted. Here's a demo:

$data = [];
$data['table']='mytable';
$data['column']='user_name';
$data['datatype']='tinyint(1)';

$rules = [
  'table' => [
    'filter' => FILTER_SANITIZE_SPECIAL_CHARS,
    'flags' => FILTER_FLAG_STRIP_LOW|FILTER_FLAG_STRIP_HIGH|FILTER_FLAG_STRIP_BACKTICK
  ],
  'column' => [
    'filter' => FILTER_SANITIZE_SPECIAL_CHARS,
    'flags' => FILTER_FLAG_STRIP_LOW|FILTER_FLAG_STRIP_HIGH|FILTER_FLAG_STRIP_BACKTICK
  ],
  'datatype' => [
    'filter' => FILTER_VALIDATE_REGEXP,
    'options' => [
      'regexp' => '/^(?:tinyint(?:\([[:digit:]]+\))|smallint(?:\([[:digit:]]+\))|int(?:\([[:digit]]+\)))|bigint(?:\([[:digit:]]+\))$/'
    ]
  ]
];


$inputs = filter_var_array($data, $rules);

$table = $inputs['table'];
$column = $inputs['column'];
$datatype = $inputs['datatype'];

$sql = "CREATE TABLE `$table` ( `$column` $datatype )";

I'll leave it as an exercise for you to build on this example to

Recognize other data types besides the integers that I put into the regular expression for the data type.
Process multiple user inputs for multiple columns.
Use filter_input_array() instead of filter_var_array() if you need this to read $_GET for user input.

Collectives™ on Stack Overflow

Php Mysql PDO prevent sql injection while creating table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related