PHP MySQL，查询错误

Question

 $sql = "UPDATE `$db_name`.`$tbl_name` SET `u_code` = '$u_code' WHERE `$tbl_name`.`email` = `$mail`;";
        $result=mysql_query($sql);

Whenever an email id (eg: [e-mail]) is entered and the query is run then error occurs (No such e-mail exists in the database , Although it is present) but after some experiments it is found that instead of entering the email-id (eg: [e-mail]) the word "email" is entered and the query is run...it works..but at the same time disturbs whole column {u_code} by changing the value to $u_code for all the database present.

So the problem seems to be in the MySQL query....but I can figure it out.

Thanks for the help! :)

Answer 1

try this:

$allowed_tables = array('table1', 'table2');
$allowed_dbnames = array('db1','db2');

if (in_array($tbl_name, $allowed_tables)) 
  && (in_array($db_name, $allowed_dbnames)) {

    $sql = "UPDATE `{$db_name}`.`{$tbl_name}` SET `u_code` = '{$u_code}' 
            WHERE `{$tbl_name}`.`email` = '{$mail}';";
    $result = mysql_query($sql);
}

first put your $xxx inside of a "" string into brackets. that makes it perfectly safe to use a variable directly inside of a string.

second: you should have written '$mail' instead of `$mail`

third: you have an SQL-injection hole that cannot be fixed using mysql_real_escape_string().
The only way to fix SQL-injection holes with dynamic table or database names is to check the input against a list of approved names, as shown in the code.
See this answer for more info: How to prevent SQL injection with dynamic tablenames?