How do I pass variables into a MySQL Query in PHP?

Question

I have the following MySQL command that tries to get login information for a user:

$requete = "SELECT login, pass FROM membres WHERE login='$login' AND pass='$pass'";
echo ("$requete");

It's not working. The problem is that, upon executing this code, I see the following output (rather than the result of executing the query):

SELECT login, pass FROM membres WHERE login='$login' AND pass='$pass'

What is the correct way to pass variables into a MySQL query in PHP?

Answer 1

In PHP, as in many other programming languages, the = operator assigns the value of the right-hand operand to the left-hand operand variable. That is, after such a command is executed, the variable named on the left of the = symbol will contain the value of the expression on the right of the = symbol.

Your right-hand operand is a double-quoted string literal, in which PHP expands (replaces) variables with their values; however, the result (which is assigned to the left-hand operand) is merely a sequence of characters.

PHP's echo construct converts its parameter(s) to string(s), concatenates them in order and sends the result to the output buffer.

In your case, the sole parameter is another double-quoted string literal "$requete", in which PHP again expands variables with their values: this time the content of the literal is solely the variable $requete, thus quoting it just caused superfluous parsing overhead; and since that variable was assigned the previous string literal, the resulting string (that is output by echo) is equal to the original string literal. Your two statements therefore have the same effect as:

echo "SELECT login, pass FROM membres WHERE login='$login' AND pass='$pass'";

PHP has not been informed that an RDBMS exists, that this is SQL which you wish to be evaluated by that RDBMS, or that you wish for the arising resultset to be output.

Worse, unless you are absolutely certain that $login and $pass do not contain unescaped ' characters, parsing that string for SQL could result in unexpected behaviour. Attackers often exploit this oversight to perform SQL injection attacks, which can compromise your entire database. The safest way to avoid this is never to evaluate variables for SQL, by instead using prepared statements into which you pass your variables as parameters.

I suggest you look into PHP Data Objects, which make safe database access very easy:

$dbh = new PDO("mysql:dbname=$dbname;charset=utf8", $username, $password);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$qry = $dbh->prepare('
  SELECT login, pass
  FROM   membres
  WHERE  login = ? AND pass = ?
');

$qry->execute(array($login, $pass));
while ($row = $qry->fetch(PDO::FETCH_ASSOC)) print_r($row);

However, some things to note:

1. You're only selecting from your database fields that match constant values, which is rather pointless: the only information one can obtain from such a query is the number of matching records in the database, but resources are wasted in returning superfluous data.

   You could instead do SELECT COUNT(*) FROM membres WHERE ... to obtain the same information without any wasted overhead; or if you only wish to discover whether there is one or more matching records, SELECT EXISTS (SELECT * FROM membres WHERE ...) would be even more efficient.

2. You appear to be creating a login script, which is non-trivial to accomplish in a secure manner: read The Definitive Guide To Forms based Website Authentication if you're interested in finding out more. There are libraries and tools out there which you can drop-in to your project to avoid you wasting resource on reinventing the wheel (and all the many painful mistakes others learnt the hard way).