Can PDO and MySQLi prepared statement prevent SQL injection even when not escaping user input?

Question

I learned that using prepared statement (parameterized queries) can be used for preventing SQL injection attack with few exceptions mentioned in this post: Are PDO prepared statements sufficient to prevent SQL injection?.

My question is how a prepared statement prevents SQL injection? What I've learned so far:

When using parameterized query, the client sends the query to server for preparation (optimization) without any data, and later, parameters (user input or any) will be sent to the server so that the user data resides outside the original query.
When using prepared query, if user data is not escaped, then it will not prevent any SQL injection.

For this reason, I failed to test the example because using execute(), bind_param(), binVlaue(), binParam() are all escaping the data when used so that we can't send un-escaped data with them for testing the safety of prepared statement with un-escaped parameters.

score 4 · Accepted Answer · answered Sep 15 '13 at 11:38

The basic problem is this:

$value = "Foo' OR 1 = 1 --";
$query = "SELECT id FROM users WHERE name = '$value'";

Concatenated as above, this is the actual query:

SELECT id FROM users WHERE name = 'Foo' OR 1 = 1 --'

The problem here is the character ', which in a string literal context has the special meaning of terminating the string literal.

One technique to deal with this is to escape characters which have a special meaning in string literals:

SELECT id FROM users WHERE name = 'Foo\' OR 1 = 1 --'

The other way is to separate the string literal out entirely, so there cannot be any confusion about where it starts and ends:

query: SELECT id FROM users WHERE name = $1
   $1: Foo' OR 1 = 1 --

The former technique is escaping, the latter is parameterized queries. You only need to use one of them. If you escape values that you also parameterize, you're just messing up your values with unnecessary backslashes.

score 2 · Answer 2 · answered Sep 15 '13 at 11:40

SQL injection means, that the user input is used as part of the SQL statement.

If you use prepared statements then the user input will be treated as a content and not as a part of the SQL command.

However if you build your SQL command by joining user input strings together, then again you are introducing SQL injection vulnerability.

So in short it is safe to use prepared queries, but the user input should be used as parameters only, not part of the query text.

score 0 · Answer 3 · answered Apr 29 '17 at 09:44

Yes, you can prevent sql injection by pdo and mysqli

<< View Demo >>

Using PDO:

Step - 1: Connect Database

Here i create database "inphplab". You can change as per your requirnment.

$dbConnection = new PDO('mysql:dbname=inphplab;host=127.0.0.1;charset=utf8', 'root', '');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Step - 2: Insert Data into table "employee"

Here i am taking two variable and declare name and address as below.

$name = "bapuRocks";
$address = "India";

$stmt = $dbConnection->prepare("INSERT INTO employee ( name, address) VALUES ( :name, :address )");

$stmt->bindValue(':name', $name);
$stmt->bindValue(':address', $address);

printf("%s Row inserted.\n", $stmt->execute());

Using MySQLi:

Step - 1: Connect Database

Here i create database "inphplab". You can change as per your requirnment.

$mysqli = new mysqli('localhost', 'root', '', 'inphplab');

/* check connection */

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

Step - 2: Insert/Delete Data into table "employee"

Here i am taking two variable and declare name and address as below.

$stmt = $mysqli->prepare("INSERT INTO employee SET `name`=?,`address`=?");
$stmt->bind_param('ss', $name, $address);

$name = 'Yuvraj Sinh';
$address = 'India';

/* execute prepared statement */
$stmt->execute();

printf("%s Row inserted.\n", $stmt->affected_rows);

/* close statement and connection */
$stmt->close();

/* Clean up table CountryLanguage */
$mysqli->query("DELETE FROM employee WHERE name='Bharat Sinh'");
printf("%s Row deleted.\n", $mysqli->affected_rows);
/* close connection */
$mysqli->close();

score 0 · Answer 4 · answered Apr 29 '17 at 10:01

Easiest way to prevent SQL injection is to convert all user input into HEX using a simple hex2str() function and then converting it back using str2hex() -- this ensures that all data will be safe.

 function strToHex($string){
      $hex = '';
      for ($i=0; $i<strlen($string); $i++){
          $ord = ord($string[$i]);
          $hexCode = dechex($ord);
          $hex .= substr('0'.$hexCode, -2);
      }
      return strToUpper($hex);
  }
  function hexToStr($hex){
      $string='';
      for ($i=0; $i < strlen($hex)-1; $i+=2){
          $string .= chr(hexdec($hex[$i].$hex[$i+1]));
      }
      return $string;
  }

EDIT

$ID = strToHex($_GET["ID"]);
$COL1 = strToHex($_GET["COL1"]);
$COL2 = strToHex($_GET["COL2"]);

$query = "insert into my_db.my_table(ID, COL1, COL2) VALUES('$ID','$COL1','$COL2')";

Simple as that

Can PDO and MySQLi prepared statement prevent SQL injection even when not escaping user input?

4 Answers4

EDIT