Converting from mysqli to prepared statements [dup

问题:

This question already has an answer here:

• When to use single quotes, double quotes, and backticks in MySQL 12 answers

I am attempting to convert code from the mysqli_* statements to prepared statements to prevent SQL injection. The following code is what I am attempting to convert (and it currently works correctly):

$details = mysqli_query($linkDB,"SELECT * FROM ".PREFIX."Issues WHERE id='".$_POST['article']."' AND disabled='0' LIMIT 1");
$detail = mysqli_fetch_assoc($details);

Here is my attempt at converting to prepared statments. Any way to make this more concise would be appreciated (since I'm going from 2 lines of code to many):

$SQL = "SELECT * FROM ".PREFIX."Issues WHERE id='?' AND disabled='0' LIMIT 1";
$PRE = mysqli_stmt_init($linkDB);
//if (! $PRE = mysqli_prepare($linkDB, $SQL)) {   (alt attempt)
    if (! mysqli_stmt_prepare($PRE, $SQL)) {
        echo "<f><msg>ERROR: Could not prepare query: ".$SQL.", ".mysqli_error($linkDB)."</msg></f>";
    } else {
        mysqli_stmt_bind_param($PRE, "i", $test);
        $test = $_POST['article'];
        if (! mysqli_stmt_execute($PRE)) {
            echo "<f><msg>ERROR: Could not execute query: ".$SQL.", ".mysqli_error($linkDB)."</msg></f>";
        } else{
            $details = mysqli_stmt_get_result($PRE);
            $detail = mysqli_fetch_assoc($details);
            mysqli_stmt_close($PRE);
        }
}

The above code does not return/store db values in the $detail variable for future processing later in the script. I have tried commenting out the mysqli_stmt_close($PRE) call, but that makes no difference. I appreciate your help!

回答1:

The main mistake in your code was the typo with '?' in your query. If the ? is inside quotes it is not treated as a placeholder, but as a literal value.

When using MySQLi you should enable MySQLi exception mode. If you do then there is no longer any need to check the result of each function. You should also use OOP style as it is less verbose and you are less likely to make a silly mistake.

// put this line before you open the MySQLi connection
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$stmt = $linkDB->prepare('SELECT * FROM '.PREFIX.'Issues WHERE id=? AND disabled=0 LIMIT 1');
$stmt->bind_param('i', $_POST['article']);
$stmt->execute();
$detail = $stmt->get_result()->fetch_assoc();

回答2:

I would recommend PDO if you aren't an experienced coder. It's object oriented and fits a modern way of coding PHP.

In your config file you put:

$db = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

And your script:

$statement = $pdo->prepare('SELECT * FROM '.PREFIX.'Issues WHERE id = :id AND disabled = 0 LIMIT 1';
$statement->execute(['id' => $_POST['article']);
$result = $statement->fetch(PDO::FETCH_ASSOC);