How can i prevent SQL injection when using MySQLi?

Question

How would i prevent SQL injections in a SQL query like this?

<?php
$mysqli = new mysqli("ip", "username", "pass", "database");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Sorry, the login server is 'Under Maintainance'");
    exit();
}

$username = $_POST["username1"];
$username = strtolower($username);
$password = $_POST['password1'];
$hash = sha1(strtolower($username) . $password);

$query = "SELECT * FROM accounts WHERE name='$username'";

if ($result = $mysqli->query($query)) {
    /* determine number of rows result set */
    $rownum = $result->num_rows;

if($rownum != 0)
{
while ($row = $result->fetch_assoc()) {
  {
  $acct = $row['acct'];
  $pass = $row['pass'];
}
if($hash == $pass){
session_start();
$_SESSION['name']=$username;
$_SESSION['acct']=$acct;
header('Location:index.php');
} else {
echo 'There was an error when logging in. Make sure your password and username are correct.';
}
}
    $result->close();
}
else
{
echo 'Account does not exist. Please <a href="register.php">Register</a> an account before logging in.';
}
    $mysqli->close();
}
?>

I have already added encryption but i cannot seem to find a prevention method that i know how to use yet. Also, is it possible for a user to use a MySQL injection without using an input box? (page dissection???)

It's impossible to prevent hacking. You'd have to hire people to guard your codebase in case somebody goes back in time to hack it before you add the protections — StackExchange User, May 04 '13 at 20:31
hahahah post is regarding sql injection, not like ... buffer overflow and all that cool stuff — Dave Chen, May 04 '13 at 20:32

score 2 · Answer 1 · answered May 04 '13 at 20:28

Encryption and query sanitation are not related.

You're already using mysqli, which is nice, but you don't sanitize the input to the query (namely $username, which probably doesn't need to be strtolowered either).

You should use properly parameterized queries for sanitation.

$query = "SELECT * FROM accounts WHERE name = ?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("s", $username);
$stmt->execute();
$stmt->bind_result($acct, $pass);
$stmt->fetch();
//$act and $pass are now properly set

The limits on SQL injection have nothing to do with the user. It's even possible for you to accidentally inject yourself in your own code, and injection does not even have to be malicious. For that reason, you should always properly parameterize your queries even if you don't think there's any risk of malicious injection.

strtolower is just something that i used so that multiple users don't have the same username. Ex: User and useR. :) — Jeremy Blevins, May 04 '13 at 20:52

How can i prevent SQL injection when using MySQLi?

1 Answers1