ExpertRefresh

Database

1) What will be the username if you want to make an SQL injection on the following code? <?php $username = $_POST['username']; $sql = "SELECT * FROM users WHERE username='{$username}' AND password='{$password}'";




2) What php function must be used as defence to SQL injection?







// 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'])) { // UNSECURE - no database filtering $username = $_POST['username']; $password = md5($_POST['password']); $sql = " SELECT * FROM users WHERE username='{$username}' AND password='{$password}'"; echo $sql; // SELECT * FROM users WHERE username='username' OR 1 = 1 #' AND ... // ON - driver-specific db filtering $username = mysql_escape_string($_POST['username']); $password = md5(mysql_escape_string($_POST['password'])); $sql = " SELECT * FROM users WHERE username='{$username}' AND password='{$password}'"; echo $sql; // Will add slashes to username quote, resulting in SQL syntax error // --- Look Here --- // } ?> <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