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](https://redbeanphp.com/index.php) which will safely create it for ya. — Lawrence Cherone, Mar 08 '18 at 20:38
Redbean? That some port of Propel to PHP or something? Or coffee themed names not just Java now :) — ficuscr, Mar 08 '18 at 20:39
Was confusing it with Hibernate... And no longer making sense — ficuscr, Mar 08 '18 at 20:44
[**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples of how to prevent SQL injection. Note however, that binding parameters only applies to values, not to column names. Thus, you will need to ensure that the column names provided by the client are valid. — Alex Howansky, Mar 08 '18 at 20:45
We are always glad to help and support new coders but ***you need to help yourself first. :-)*** After [**doing more research**](https://meta.stackoverflow.com/q/261592/1011527) if you have a problem **post what you've tried** with a **clear explanation of what isn't working** and provide [a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). Read [How to Ask](http://stackoverflow.com/help/how-to-ask) a good question. Be sure to [take the tour](http://stackoverflow.com/tour) and read [this](https://meta.stackoverflow.com/q/347937/1011527). — Jay Blanchard, Mar 08 '18 at 20:46
Also note, dynamically creating tables at runtime based on client input is a [code smell](https://en.wikipedia.org/wiki/Code_smell). The legitimate need to do that is quite rare. — Alex Howansky, Mar 08 '18 at 20:47
I agree with @AlexHowansky — creating tables based on user input is dangerous. The risk is that users will create thousands of bogus tables. — Bill Karwin, Mar 09 '18 at 23:31

Bill Karwin · Accepted Answer · 2018-03-09T23:30:42.223

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.

Php Mysql PDO prevent sql injection while creating table

1 Answers1