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 table form with a username and password field and a submit button. The PHP code used with the mySQL database is as follows:

$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 try to implement prepared statements by looking at the W3S website and others. I guess 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 not sure of the validity of the amendments. In addition, to check whether the vulnerability of the system has been fixed, how can I access the system through the original, unmodified code? I tried:

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

Answer 1

The best way to prevent first, second, and third order injections, I suggest you use PDO as well as prepared instructions when using the correct encoding and turn off the "Emulated Prepared Statement". More information on how SQL injection works and how PDO can prevent it can be found here .