How Can I Securely Retrieve Data from MySQL Using Prepared Statements in PHP?

Secure MySQL Prepared Statements in PHP

When working with SQL queries in PHP, it's crucial to prioritize security by using prepared statements. Here's how to create a secure prepared statement to retrieve columns using input from URL parameters:

Utilize MySQL Improved (MySQLi):

$db = new mysqli("host", "user", "pw", "database");
$stmt = $db->prepare("SELECT * FROM mytable WHERE userid = ? AND category = ? ORDER BY id DESC");
$stmt->bind_param('ii', intval($_GET['userid']), intval($_GET['category']));
$stmt->execute();

$stmt->store_result();
$stmt->bind_result($column1, $column2, $column3);

while ($stmt->fetch()) {
    echo "col1=$column1, col2=$column2, col3=$column3 \n";
}

$stmt->close();

Alternatively, you can use this helper function to simplify the binding of associative arrays:

function stmt_bind_assoc (&$stmt, &$out) {
    $data = mysqli_stmt_result_metadata($stmt);
    $fields = array();
    $out = array();

    $fields[0] = $stmt;
    $count = 1;

    while ($field = mysqli_fetch_field($data)) {
        $fields[$count] = &$out[$field->name];
        $count++;
    }
    call_user_func_array(mysqli_stmt_bind_result, $fields);
}

To use it:

$stmt->store_result();

$resultrow = array();
stmt_bind_assoc($stmt, $resultrow);

while ($stmt->fetch()) {
    print_r($resultrow);
}

Performance Enhancement

Prepared statements do enhance performance by minimizing the need for re-parsing and re-planning of queries. However, the impact on speed may not be significant if used only a few times on a single page. Nevertheless, it remains a recommended practice for increased security and protection against SQL injection vulnerabilities.

The above is the detailed content of How Can I Securely Retrieve Data from MySQL Using Prepared Statements in PHP?. For more information, please follow other related articles on the PHP Chinese website!