# 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.
![](https://raw.githubusercontent.com/shawnduong/ctf-writeups/master/2019-TAMU/images/SQL-1.png)
