Tags: programming secure-coding
Rating:
# SQL
In one of the previous challenges, we exploited an SQL injection. Now, we have to fix the code to be SQLi resistant.
This is the vulnerable program:
```php
";
if (isset($_POST["username"]) && isset($_POST["password"])) {
$servername = "localhost";
$username = "sqli-user";
$password = 'AxU3a9w-azMC7LKzxrVJ^tu5qnM_98Eb';
$dbname = "SqliDB";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error)
die("Connection failed: " . $conn->connect_error);
$user = $_POST['username'];
$pass = $_POST['password'];
$sql = "SELECT * FROM login WHERE User='$user' AND Password='$pass'";
if ($result = $conn->query($sql))
{
if ($result->num_rows >= 1)
{
$row = $result->fetch_assoc();
echo "You logged in as " . $row["User"];
$row = $result->fetch_assoc();
echo "<html>You logged in as " . $row["User"] . "</html>\n";
}
else {
echo "Sorry to say, that's invalid login info!";
}
}
$conn->close();
}
else
echo "Must supply username and password...";
echo "</html>";
?>
```
The vulnerability lies in these lines right here.
```php
$user = $_POST['username'];
$pass = $_POST['password'];
$sql = "SELECT * FROM login WHERE User='$user' AND Password='$pass'";
```
The solution is to escape the user inputs. We can do this using `mysqli_real_escape_string()`. This function will take our connection (`$conn`) as well as the `$_POST[]`. This, we take these three lines of code and use the escape function on it to escape user inputs to thwart any possibly problematic code-breaking.
```php
$user = mysqli_real_escape_string($conn, $_POST['username']);
$pass = mysqli_real_escape_string($conn, $_POST['password']);
$sql = "SELECT * FROM login WHERE User='".$user."' AND Password='".$pass."'";
```
(That third line is just a stylistic preference of mine. No idea if it actually makes a difference.)
Our secure program, now, is:
```php
";
if (isset($_POST["username"]) && isset($_POST["password"])) {
$servername = "localhost";
$username = "sqli-user";
$password = 'AxU3a9w-azMC7LKzxrVJ^tu5qnM_98Eb';
$dbname = "SqliDB";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error)
die("Connection failed: " . $conn->connect_error);
$user = mysqli_real_escape_string($conn, $_POST['username']);
$pass = mysqli_real_escape_string($conn, $_POST['password']);
$sql = "SELECT * FROM login WHERE User='".$user."' AND Password='".$pass."'";
if ($result = $conn->query($sql))
{
if ($result->num_rows >= 1)
{
$row = $result->fetch_assoc();
echo "You logged in as " . $row["User"];
$row = $result->fetch_assoc();
echo "<html>You logged in as " . $row["User"] . "</html>\n";
}
else {
echo "Sorry to say, that's invalid login info!";
}
}
$conn->close();
}
else
echo "Must supply username and password...";
echo "</html>";
?>
```
Now, all we have to do is fork the main repository, add our secure program, commit, and push it for the code to be checked.
