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());
精彩评论