CODEWITHSUNDEEP
phpmysqliprepared-statementsql-injection
Louis Loudog Trottier
Louis Loudog Trottier

Reputation: 1377

SQL injection and prepared statement, when does it get overkill?

I've read alot about sql injection and i've been using mysqli prepared statement for over a year now. The closer i got to my question was this one Why does this MySQLI prepared statement allow SQL injection?

Now, i want to create a function to run a query based on search criteria from the user. I'm using this so i could used lots of different criteria.

Here a simplified example so you can wrap your head around my question: Let says we have two tables, One with tires and one with Wheels.

CREATE TABLE IF NOT EXISTS `wheels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `size` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `brand` (`brand`),
  KEY `size` (`size`),
  KEY `price` (`price`)
);
CREATE TABLE IF NOT EXISTS `tires` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `brand` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `size` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `brand` (`brand`),
  KEY `size` (`size`),
  KEY `price` (`price`)
);

Now we have a form to let the user make his search

<form method='post' ...>
  Looking for : <select name='item'>
    <option value='tires'>Tires</option>
    <option value='wheels'>Wheels</option>
  </select>
  Search by : <select name='type'>
    <option value='size'>Size</option>
    <option value='price'>Maximum price</option>
  </select>
  <input type='text' name='criteria' />
</form>

Now the php to handle the request would look something like this:

$item=filter_input(INPUT_POST,'item',FILTER_SANITIZE_STRING);
$type=filter_input(INPUT_POST,'type',FILTER_SANITIZE_STRING);
$criteria=filter_input(INPUT_POST,'criteria',FILTER_SANITIZE_NUMBER_INT);

function build_query($item,$type,$criteria){
  switch($item){
    case 'tires': $table='tires'; break;
    case 'wheels': $table='wheels'; break;
    defaults: /*error handling : bad search criteria*/ break;
  }
  switch($type){
    case 'size': $field='size'; $operator='='; break;
    case 'price': $field='price'; $operator='<='; break;
    defaults: /*error handling : bad search criteria*/ break;
  }
  $value=intval($criteria);

  $sql= ....

  //Do the rest of sqli magic here and return the results.

}

PLEASE TAKE NOTE that only the $criteria (that become $value) is not 'hard coded' and sent to the DB from the search form. SO THE QUESTION WOULD BE : If the $table, $field and $operator variable are from my php internal code, is it necessary to bind them as well?

In other words:

Is this overkill?

$sql="SELECT * FROM ? WHERE ???"; 
$stmt->bind_param('sssi',$table,$field,$operator,$value);

AND / OR Is this sufficient?

$sql="SELECT * FROM ".$table." WHERE ".$field.$operator."?";
$stmt->bind_param('i',$value);

As mentioned before, this is an simplified example so you have a better understanding of the question.

Upvotes: 0

Views: 150

Answers (1)

user1032531
user1032531

Reputation: 26281

Yes, you are going to far using PDO. PDO is only used for parameters, and not for tables or operators. Use your second query.

http://php.net/manual/en/pdostatement.bindparam.php does not indicate that tables and operators are allowed, and I recall testing this a while back, and found that they are not.

Binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement. Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

Most parameters are input parameters, that is, parameters that are used in a read-only fashion to build up the query. Some drivers support the invocation of stored procedures that return data as output parameters, and some also as input/output parameters that both send in data and are updated to receive it.

Upvotes: 1

Related Questions