The new MySQL extension offers a way to both avoid SQL injection and to speed up SQL statements: so-called prepared statements. Within them, you provide placeholders for any dynamic data you are using in the SQL code. You then assign values to those placeholders. The MySQL extension then takes care of all the rest, including escaping of special characters. Using Prepared Statements with MySQL (mysqli_stmt_execute.php; excerpt)<?php if ($db = @mysqli_connect('localhost', 'user', 'password')) { require_once 'stripFormSlashes.inc.php'; mysqli_select_db($db, 'phrasebook'); $stmt = mysqli_prepare($db, 'INSERT INTO quotes (quote, author, year) VALUES (?, ?, ?)'); $quote = mysqli_real_escape_string($db, $_POST['quote']); $author = mysqli_real_escape_string($db, $_POST['author']); $year = intval($_POST['year']); mysqli_stmt_bind_param($stmt, 'ssi', $quote, $author, $year); if (mysqli_stmt_execute($stmt)) { echo 'Quote saved.'; } else { echo 'Error writing quote.'; } mysqli_close($db); } else { echo 'Connection failed.'; } ?> So first, you prepare an SQL statement with mysqli_prepare(); as a placeholder character, you use a question mark: $stmt = mysqli_prepare($db, 'INSERT INTO quotes (quote, author, year) VALUES (?, ?, ?)'); Then, you bind values to each parameter. First, you provide the statement returned by mysqli_prepare, then one-character codes for the values of all parameters (s for string, i for integer, d for double). Finally, you provide a list of values. Because these values are used by reference, you have to provide variables, not raw values. mysqli_stmt_bind_param($stmt, 'ssi', $quote, $author, $year); Finally, mysqli_stmt_execute() executes the prepared statement.
|