SQL queries using SQL Injections do not work as I think they should

Question

I am trying to learn about SQL Injections. I have created a login form using PHP (I am quite new to PHP) as backend scripting language and MySQL as DBMS. Last day I asked a question about SQL Injections: SQL Injection not working when I think it should, and it solved me some doubts, but now I have new doubts:

What I am trying to achieve is to bypass the login form that I have created, using SQL Injections. This is the script to check if the entered username and password belong to a user already registered (stored in the DB).

$email = $_POST['u_email'];
$password = $_POST['u_password'];

$query = "SELECT * FROM User WHERE EMAIL='$email' AND PASSWORD='$password'";
$result = $mysqli->query($query);
$counter = mysqli_num_rows($result);

echo "Counter: " . $counter . '<br>';
echo "Query: " . $query;
if($counter == 1)//Correct Login

I have to say that I am really confused about the result gotten when running the queries. I have created the following 3 examples where in each of them I insert different username and password values in the login form and the true is that I do not understand the gotten result of any of the examples, I think the results should be different.

1 Example: ([email protected] is an existing email in the User table)

Input
Username: [email protected]' OR '1'='1/*
Password: */

Output
Counter: 1
Query: SELECT * FROM User WHERE EMAIL='[email protected]' OR '1'='1/*' AND PASSWORD='*/'

Should not the $counter variable value be 30 (30 is the number of registered users in the User table)?

When I run the following query in the MySQL CLI:

SELECT * FROM User WHERE EMAIL='[email protected]' OR '1'='1'

I get 30 rows, so why $counter value is 1 and not 30?

2 Example: (xxx is NOT an existing email in the User table)

Input
Username: xxx' OR '1'='1/*
Password: */

Output
Counter: 0
Query: SELECT * FROM User WHERE EMAIL='xxx' OR '1'='1/*' AND PASSWORD='*/'

Should not the $counter variable value be 30 (30 is the number of registered users in the User table)?

When I run the following query in the MySQL CLI:

SELECT * FROM User WHERE EMAIL='xxx' OR '1'='1'

I get 30 rows, so why $counter value is 0 and not 30?

3 Example: ([email protected] is an existing email in the User table)

Input
Username: [email protected]/*
Password: */

Output
Counter: 0
Query: SELECT * FROM User WHERE EMAIL='[email protected]/*' AND PASSWORD='*/'

Should not the $counter variable value be 1?

When I run the following query in the MySQL CLI:

SELECT * FROM User WHERE EMAIL='xxx' OR '1'='1'

I get 1 row, so why $counter value is 0 and not 1?

Answer 1

Example 1 & 2

This is because your query is applying this clause:

'1'='1/*'

If you have a comment character inside of your quotes the comment is not respected by the query parser, you would have to exit the quoted context first.

1 is returned as count because that's how many users have that email address.

Try this instead for example 1 (the same technique will apply to the others):

Username: [email protected]' OR '1'='1'; --
Password: Whatever

This should make the query

SELECT * FROM User WHERE EMAIL='[email protected]' OR '1'='1'; -- ' AND PASSWORD='Whatever'

Example 3

I'm guessing that you do not have a user with the email address [email protected]/* set? Again, because of the quotes, it will be querying [email protected]/* and not [email protected].