SQL prepared statement (PHP)

Question

I am trying to understand how SQL injection works, and how to prevent it. The HTML login page contains a form, as a table, with a username and password field, and a submit button. The PHP code, used with a mySQL database, looks like this:

$conn = mysqli_connect($Host, $User, $Password, $DbName);
if (!$conn) {
 echo "Database connection error.";
 exit;
}
$query = "SELECT user_name, password from visitors where user_name = '".$_POST['user_name']."';";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);
$user_pass = md5($_POST['pass_word']);
$user_name = $row['user_name'];
if(strcmp($user_pass,$row['password']) != 0) {
 echo "Login failed";
}

To prevent an SQL injection attack, I am trying to implement prepared statements, having had a look at the W3S website, and others. I assume I will need to replace

$query="SELECT user_name, password from visitors where user_name='".$_POST['user_name']."';";

with something like this:

$stmt = $conn->prepare("SELECT user_name, password from visitors where  user_name= ?");
if ($stmt->execute(array($_GET[‘user_name’]))) {
  while ($row = $stmt->fetch()) {
    $user_name = $row;
  }
}

I am uncertain about the validity of the amendment. Also, in order to test whether the vulnerability of the system has been addressed, how would I be able to gain access to the system via the original, unmodified, code? I tried:

username: admin
password: ‘ or ‘1’=’1’ (and a number of other options too)

if that is your actual `‘ or ‘1’=’1’` those are called curly quotes and will result in a parse error. Use regular quotes `'`. Unless that's just a bad paste. — Funk Forty Niner, Aug 26 '15 at 13:34
That injection won't "gain access" to the system since the password comparison will still fail. But instead of logging in with the username `admin`, imagine logging in with the username: `';drop table visitors;--` — David, Aug 26 '15 at 13:34
[W3Schools SQL tutorial](http://www.w3schools.com/sql/sql_injection.asp) has good examples of basic SQL injections if you want to try and test it. — , Aug 26 '15 at 13:34
More info on SQL injection and PHP at: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — CD001, Aug 26 '15 at 13:35
Also this: https://xkcd.com/327/ ... which needs to be a comment somewhere on every question ever asked on SQL injection — CD001, Aug 26 '15 at 13:39
It is also worth it to look at this in general https://www.owasp.org/index.php/Top_10_2013-A1-Injection and top 10 attack types https://www.owasp.org/index.php/Category:OWASP_Top_Ten_Project — Maytham Fahmi, Aug 26 '15 at 16:06
It's good to see that people concern about security, I would just recommend to use something more safe than md5, it's a very weak hash solution, and if it happens that some data gets stolen, it's very easy to recover the passwords of all users. — Gogolex, Aug 28 '15 at 11:57
Valid point about md5. A friend mentioned this yesterday as being an issue. — cadebe, Aug 28 '15 at 22:08
it is worth looking this link https://www.owasp.org/index.php/Top_10_2013-A1-Injection — Maytham Fahmi, Aug 29 '15 at 20:27

score 1 · Accepted Answer · edited May 23 '17 at 12:06

1

The best way to prevent first, second and third order injections, I'd suggest you to use PDO as well as prepared statements while using the proper charset and disable "Emulated Prepared Statements". More information about how an SQL injection works and how PDO can prevent it can be found here.

edited May 23 '17 at 12:06

Community

1
1

answered Aug 29 '15 at 20:00

manniL

7,157
7
46
72

This video was phenomenally helpful in understanding PDO and prepared statements: https://www.youtube.com/watch?v=GBDbclDfc84 – cadebe Sep 03 '15 at 19:44

SQL prepared statement (PHP)

1 Answers1