Studyon Minte9.com
ZCE 5.3

Study

Database security



               // Atack: username' OR 1 = 1 #
               // Defence: mysql_escape_string()
               // Best: binding; mysqli_stmt (prepared statement)


* SQL injection

Occurs when a malicious user experiments on a form to gain information about a database.

<?php if (isset($_POST['btn_submit'])) { $username = $_POST['username']; $password = md5($_POST['password']); // no database filtering - UNSECURE $sql = " SELECT * FROM users WHERE username='{$username}' AND password='{$password}' "; echo $sql; // SELECT * FROM users WHERE username='username' OR 1 = 1 #' AND ... // driver-specific db filtering - OK $sql = mysql_escape_string($sql); echo $sql; // SELECT * FROM users WHERE username='username' OR 1 = 1 #' AND ... } ?> <form method="POST"> Username: <input type="text" name="username" value="username' OR 1 = 1 #"/> Password: <input type="password" name="password" /> <input type="submit" name="btn_submit" value="Log In"/> </form>
  Since 1 = 1 is always true and - begins an SQL comment, the SQL query ignores everything after the - and successfully returns all user records. To escape output for an SQL query, use the driver-specific *_escape_string() function for your database. If possible, use bound parameters. * Bind parameters Use a placeholder like ?, :name or @name and provide the actual values using a separate API call. There are, two good reasons to use bind parameters in programs: * Security Bind variables are the best way to prevent SQL injection. * Performance Not using bind parameters is like recompiling a program every time. * MySQLi The mysqli_stmt class in PHP 5 are used for prepared statements. * Without bind parameter:
$mysqli->query("SELECT * FROM employees WHERE id = " . $id);
* With bind parameter:
$stmt = $mysqli->prepare("SELECT * FROM employees WHERE id = ?"); $stmt->bind_param("i", $id); $stmt->execute();
The MySQLi extension provides various benefits with respect to its predecessor: * An object-oriented interface * Support for prepared statements * Support for multiple statements * Support for transactions * Enhanced debugging support * Embedded server support


http://use-the-index-luke.com/sql/where-clause/bind-parameters
http://www.php.net/manual/en/pdo.prepared-statements.php