Use of multiple conditionals in an SQL query

Question

I’m wondering how to perform a query with different conditionals. The query is a login validation, I need to check if the data contained in the user input is equal to the username or email or Cpf and if the password is correct. I used this query

SELECT * FROM student WHERE username = '$user' OR email = '$user' OR cpf = '$user' AND password = '$password'

But when the password is incorrect it still returns the user. If I could break it up into parentheses like in math operations, I’d need something like this to work

SELECT * FROM student WHERE (username = '$user' OR email = '$user' OR cpf = '$user') AND password = '$password'

someone knows how to do this check?

Answer 1

There’s nothing wrong with having ( ) in the database queries and the same works like anywhere else, even as in the mathematics we learn in school.

As there are no examples of database and input data, there is no certainty, but the lack of data can be explained by the use of equality in the comparison of strings. When comparing text data from the rare database are the times you should use the = to the detriment of LIKE.

Other reasons for the query not to return data may also be divergences in collations and uppercase/lowercase. It is necessary to check the data and input, as well as what you want to return in each case.

Now, what really seems to be happening is that the query is searching for username = $user and not the value of $user. You need to change the string to use string Interpolation, for example. The language where the query is made is not in the question, but could give the example with PHP (already with the change to use the LIKE):

var $query = "SELECT * FROM student WHERE (username LIKE '". $user ."' OR email LIKE '". $user ."' OR cpf LIKE '". $user ."') AND password LIKE '". $password ."'";

The big problem of the one presented in the question, as exposed in the comments is even the way s is made the query, allowing, for example, to do SQL Injection. The query should be replaced by prepared statements.

Another problem also seems to be in relation to passwords, which by the presented, appear to be stored in the database. We should never store "open" passwords in the database. One should use strong encryption mechanisms (SHA256 or SHA512, for example) and store the output of these functions. In doing so, you need to change your query to first search for the student, without looking at the password, using only the part shown in parentheses (username LIKE '". $user ."' OR email LIKE '". $user ."' OR cpf LIKE '". $user ."') and then use an encrypted string comparison function to compare user input already encrypted with what’s in the database.

Again using PHP as an example:

$hashedPasswordFromPost = hash('sha512', $_POST['password']); // SENHA UTILIZADA NO FORM
$hashedPasswordFromDB = ...; // SENHA QUE ESTÁ NO BD

if (password_verify($hashedPasswordFromPost, $hashedPasswordFromDB)) {
  // Usuário válido
} else {
 // Usuário ou senha incorreto
}