Preventing the 1'or'1'='1 SQL injection using mysqli real escape string

Home > Search
  by

There are numerous reasons to use the mysqli_real_escape_string.  Most importantly, using the mysqli_real_escape_string can prevent the 1'or'1'='1 SQL injection attack. Let's say we have a MySQL table setup where the id is 1, the username is mrbill and the password is billspassword.  Of course, Bill would be able to sign in using username mrbill and password billspassword.  However, if we are not using the mysqli_real_escape_string, a nefarious hacker may be able to sign in by entering 1'or'1'='1 into the username and password fields.

Most authentication systems using a count mechanism to determine if a user is or is not authenticated.  When the count is 1, the user is authenticated.  When the count is 0, the user is not authenticated.  For example, if a match is found in MySQL with username mrbill and password billspassword, the count will be set to 1. 

select count(*) from users where username='mrbill' and password='billspassword';
count = 1;

 

If a match is not found in MySQL with username mrbill and password billspassword, the count will be set to 0. 

select count(*) from users where username='msamy' and password='amysspassword';
count = 0;

 

By entering 1'or'1'='1 in the username and password fields, the MySQL query check for an entry in MySQL where there is a username 1 and password 1, OR where there is an id of 1.  We would not have any matches where there is a username and password of 1, however we will have a match where there is an id of 1.  Because there is a match where there is an id of 1, the count will be set to 1, and the user will be authenticated.

select count(*) from users where username='1'or'1'='1' and password='1'or'1'='1';
count = 1;

 

We can prevent this attack by using the mysqli_real_escape_string.  By placing the mysqli_real_escape_string on the username and password variables in PHP, the apostrophes will be escaped, and the count will be zero.

$username= mysqli_real_escape_string($con, $_GET['username'];
$password = mysqli_real_escape_string($con, $_GET['password'];

select count(*) from users where username='1\'or\'1\'=\'1' and password='1\'or\'1\'=\'1'
count = 0;


Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter in the box below so that we can be sure you are a human.




Comments