Why does this MySQLI prepared statement allow SQL

问题:

As I was teaching students how to prevent SQL injection today, I was mildly embarrassed. In professional projects I've used prepared statements / parameterized queries as one layer of prevention against SQL injection (although I've never used mySQL professionally). In theory, I thought SQL injection was impossible when using a prepared statement.

But then this worked...

$Search = $_GET['s'];
$stmt = $mysqli->prepare("SELECT * FROM products WHERE id = ?");
$stmt->bind_param("i", $Search);
$stmt->execute();
$Results = $stmt->get_result();

If I pass the parameter "?s=1 OR 1=1" then I can get a full listing of all products. I still can't insert another query at the end, but I am confused about why this type of basic SQL injection is possible in mysql/php. I assumed that the parameter "1 OR 1=1" would be rejected because it isn't numeric (obviously I could run that numeric check ...).

Can anyone explain why this works, and what I don't understand about prepared statements in php/mysql?

My school computer has an out-of-the-box Zend WAMP installation btw.

EDIT: This is the string value that is causing my confusion:

$Search = "1 OR 1=1";

回答1:

That's ok, no any kind of SQL injection here, you do not see list of all products (at least code you provided cannot show it)

you do not need to cast to int, lets go deeper what bind_param actually do:

it has string "i" which means 1 integer argument

you're passing value from $_GET which is string

bind_param tries to convert String to int, so check this php code:

echo intval('a', 10); // output 0
echo intval('1a', 10); // output 1
echo intval('12a', 10); // output 12
echo intval('b1', 10); // output 0
echo intval('1 or 1=1', 10); // output 1
echo intval("?s=1 OR 1=1", 10); // output 0

so, you output products with id=1, maybe you have some of them?

回答2:

I tried to reproduce your case, but couldn't.

Database

CREATE TABLE `Document` (
  `DataID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Description` varchar(50) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`DataID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `Document` VALUES (1,'BI8392');
INSERT INTO `Document` VALUES (2,'HE8492');
INSERT INTO `Document` VALUES (3,'HE8493');
INSERT INTO `Document` VALUES (4,'HE8490');

Code

<?php
$host = "localhost";
$passwd = "";
$user = "user";
$bdd = "test";

$conn = mysqli_connect( $host, $user, $passwd, $bdd);
mysqli_set_charset($conn, "utf8");

if (!$conn) {
   printf('Error connecting to mysql. Error code: ', mysqli_connect_error());
   exit;
}

$sql = "SELECT * FROM Document WHERE DataID = ?";
if ($stmt = $conn->prepare($sql)) {

    $param = "2 OR 1=1";
    $stmt->bind_param("i", $param);
    $stmt->execute();
    $results = $stmt->get_result();
    $data = mysqli_fetch_all($results);

    var_dump($data);
}

Output

array(1) {
  [0]=>
  array(2) {
    [0]=>
    int(2)
    [1]=>
    string(6) "HE8492"
  }
}

Seems, that 1=1 condition was ignored.