<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("server", "username", "password", "database_name");

$variable = $_POST["user-input"];
$stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");
// "s" means the database expects a string
$stmt->bind_param("s", $variable);
$stmt->execute();

The key function you need to read is mysqli::prepare.

Also, as others have suggested, you may find using something like PDO.

Please note that the case you are asking about is fairly simple, more complex cases may require more sophisticated methods. in particular:

• If you want to change the SQL structure based on user input, parameterized queries will not help, and mysql_real_escape_string does not contain the required escapes. In this case, you'd be better off passing the user's input through a whitelist to ensure only "safe" values ​​are allowed through.

Like +0

Add Reply 关闭回复

P粉022723606

reply

1. Using

   PDO (for any supported database driver):

   $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
   $stmt->execute([ 'name' => $name ]);
   
   foreach ($stmt as $row) {
       // Do something with $row
   }
   

2. Using

   MySQLi (for MySQL):
   Starting with PHP 8.2, we can use execute_query() to prepare, bind parameters and execute SQL statements in one method:

   $result = $db->execute_query('SELECT * FROM employees WHERE name = ?', [$name]);
    while ($row = $result->fetch_assoc()) {
        // Do something with $row
    }
   

   Up to PHP8.1:

    $stmt = $db->prepare('SELECT * FROM employees WHERE name = ?');
    $stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        // Do something with $row
    }
   

pg_prepare() and pg_execute() for PostgreSQL) . PDO is a universal option.

PDO to access a MySQL database, real prepared statements are not used by default . To resolve this issue, you must disable simulation of prepared statements. An example of creating a connection using PDO is:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8mb4', 'user', 'password');

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

but it is recommended to add it . This way, PDO will notify you of all MySQL errors by throwing PDOException.

forcing is the first setAttribute() line, which tells PDO to disable simulated prepared statements and use real prepared statement statements . This ensures that statements and values ​​are not parsed by PHP before being sent to the MySQL server (giving a would-be attacker no chance to inject malicious SQL).

character set in the options of the constructor, it is important to note that "older" versions of PHP (prior to 5.3.6) silently ignore the Character set parameters .

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // error reporting
$dbConnection = new mysqli('127.0.0.1', 'username', 'password', 'test');
$dbConnection->set_charset('utf8mb4'); // charset

prepare are parsed and compiled by the database server. You tell the database engine where you want to filter by specifying parameters (? or named parameters, like :name in the example above). Then, when you call execute, the prepared statement is combined with the parameter values ​​you specified.

Any parameters you send when using prepared statements will be treated as strings (although the database engine may do some optimizations, so parameters may of course end up being treated as numbers as well). In the example above, if the $name variable contains 'Sarah'; DELETE FROMEmployees the result is simply the search string "'Sarah'; DELETE FROMEmployees", and You will not end up with an empty table.

Another benefit of using prepared statements is that if you execute the same statement multiple times in the same session, it will only be parsed and compiled once, thus improving speed.

Oh, since you asked how to do the insert, here's an example (using PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute([ 'column' => $unsafeValue ]);

Can prepared statements be used for dynamic queries?

While you can still use prepared statements with query parameters, the structure of the dynamic query itself cannot be parameterized, and certain query functions cannot be parameterized either.

For these specific scenarios, the best approach is to use a whitelist filter to limit the possible values.

// Value whitelist
// $dir can only be 'DESC', otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
   $dir = 'ASC';
}

Like +0

Add Reply 关闭回复

P粉022723606

reply