这三个sql查询之间有什么区别？

What's the difference between these three queries, in terms of security but also good coding syntax?

Query 1

$sql = "SELECT user_id,
               user_email,
               user_pass
          FROM user_tb 
         WHERE user_email =\"".$e."\" 
           AND user_pass = md5(\"".$p."\") ";

Query 2

$sql = "SELECT user_id,
               user_email,
               user_pass
          FROM user_tb 
         WHERE user_email = '$e' 
           AND user_pass = '$p' ";

Query 3

$sql = "SELECT user_id,
               user_email,
               user_pass
          FROM user_tb 
         WHERE user_email = $e 
           AND user_pass = $p ";

The first one performs string concatenation for the variables ($e and $p), additionallying using the MD5 function to get the hash of the password for comparison against the USER_TB.user_pass column value. The MD5 isn't any good if the values aren't stored as the hash - because there wouldn't be anything to match against. Same for vice versa...

The second example lacks the MD5 function use, but properly encloses the variables in single quotes to be treated as string literals for comparison against column values.

The third query lacks the single quoting for proper SQL string handling, and the MD5 function use.

Summary

All three are susceptible to SQL injection attacks. If not using PDO, sprintf is a solution to using a prepared statement:

$query = sprintf("SELECT t.user_id,
                         t.user_email,
                         t.user_pass
                    FROM USER_TB t
                   WHERE t.user_email = '%s' 
                     AND t.user_pass = '%s'",
                  mysql_real_escape_string($e),
                  mysql_real_escape_string($p));

$result = mysql_query($query);

All three have effectively no security and are susceptible to any sql injection. You can use mysql_real_escape_string() around your variables, or use prepared statements to make this safe.

That being said, placing quotes around your $e and $p variables will only ensure that you don't have a sql error in case those values are null.

They all fail in my book unless $e and $p have both already been sanitized offscreen. If that is the case, then #2 wins. MD5 is a weak hash. PHP can perform much stronger hashing with hash_hmac() sha512, or other libraries (everyone will have something so say) compared to MySQL. If you are using md5() regardless, I would imagine php will perform faster than MySQL here, but that's not a security issue. Other than that, #1 and #2 are tied.

The problem with #3 is a circumstance where $e or $p might be empty for whatever reason. Your query will be where user_email= AND user_pass and you will get a nasty MySQL syntax error. They will also need to be wrapped in quotes in addition to other sanitation they might receive or the sanitation isn't going to do you any good.

Edit: as OMG Ponies points out, you need to have consistency with the md5() (or whatever hash) check. It is my assumption that the password hash is stored and in queries #2 and #3 above, $user_pass is hashed by php before being used in the query.

I like "Query 2" too. And it has 0 security, this is vulnerable to SQLInjection.

You should sanitize the $e and $p strings and make sure it has no bad characters like + /* -- ' " etc