开发者

MYSQL WHERE clause is wrong value

Whenever I try to perform my qu开发者_JAVA技巧ery, It gives me an unknown column error, because it is using my variable as the column name.

essentially

$search="lname";
$term="asdas";

(both of those are variables from a form on another page)

I run this:

if (isset($term))
    {
    $query = "SELECT * FROM test 
    WHERE $search = $term ";
    }
else
    {   
    $query = "SELECT * FROM test";
    }
echo $query;    
$result=mysql_query($query) or die(mysql_error());

and then I get this as my error: Unknown column 'asdas' in 'where clause'


You need to enclose the search term in single quotes(also use mysql_real_escape_string to avoid any issues with quotes in the search string.). i.e:

if (isset($term))     
{     
    $query = "SELECT * FROM test WHERE $search = '" . mysql_real_escape_string($term) . "' ";     
}


You need to quote it.

if (isset($term))
    {
    $query = "SELECT * FROM test 
    WHERE $search = '$term' ";
    }
else
    {   
    $query = "SELECT * FROM test";
    }
echo $query;    
$result=mysql_query($query) or die(mysql_error());

Other comments

It is always better to use parameterized queries if the driver supports it. It will prevent SQL injection. As it stands, someone could send in a string "' or ''='" and the query turns out to be

SELECT * FROM test WHERE col1 = '' or ''=''

which is really benign but unexpected behaviour. If the string contains single quotes, it also breaks your query (input is "o'neil")

SELECT * FROM test WHERE col1 = 'o'neil'   # << unmatched quotes

So, at the very least use mysql_real_escape_string if you cannot use parameters, i.e.

    $query = "SELECT * FROM test 
    WHERE $search = '" . mysql_real_escape_string($term) . "' ";


You need to quote your $term parameter:

// protect from trivial sql injection attacks.
$term = mysql_real_escape_string("adas");
$query = "SELECT * FROM test 
    WHERE $search = '$term'";


You have to surround the term value with quotes:

SELECT *
FROM test
WHERE lname='asdas'

otherwise any SQL server out there will think asdas is a field name and try to find it in the table.


Add ' around your columns

$query = "SELECT * FROM test WHERE $search = '$term' ";


you need to put single quotes around $term so that the SQL thinks it's a string


put single quote string always be quoted. Do not forgot use mysql_real_escape_sring()

 $query = "SELECT * FROM test 
    WHERE $search = '$term' ";


Put single quotes around $term

if (isset($term))
{
$query = "SELECT * FROM test WHERE $search = '$term'";
}
else {

$query = "SELECT * FROM test";
}
echo $query;
$result=mysql_query($query) or die(mysql_error());

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜