Uncaught mysqli_sql_exception: Unknown column in 'where clause'

Question

I have a problem with my php code when i try to choose a string in my database. Well, I have a table "news" and a field "category" (TINYTEXT type) there. And I try to display a string from this table by using "where clause":

$Sql = 'SELECT * FROM `news` WHERE `category` = '.$Param['category'].' ORDER BY `id` DESC';

I must say that I received "$Param['category']" by using URL Path: 1

So, if my "category" value of any string in table has any string type value like "games" 2, 3 - string from my table isn't displayed with this error "Fatal error: Uncaught mysqli_sql_exception: Unknown column 'games' in 'where clause'"

But, if I change the "category" value of string in the database into anything numeral value - the string from my table is displayed correctly!4, 5

What's wrong with my code? Thank you in advance! Sorry for the mistakes.

Answer 1

You need to use prepared queries, both for the sake of SQL injection protection, and because it will fix your bug. I believe the issue is that you don't have any quotes at all around your parameters. As a result you're building queries like this:

SELECT * FROM `news` WHERE `category` = games ORDER BY `id` DESC
SELECT * FROM `news` WHERE `category` = 1 ORDER BY `id` DESC

The latter is a valid query: mysql looks for records where the category is 1. The former is not: the query tries to find records where the column category matches the column games. The latter doesn't exist so you get an error. You want to build these queries:

SELECT * FROM `news` WHERE `category` = 'games' ORDER BY `id` DESC
SELECT * FROM `news` WHERE `category` = '1' ORDER BY `id` DESC

Which you would do with this code:

$Sql = "SELECT * FROM `news` WHERE `category` = '".$Param['category']."' ORDER BY `id` DESC";

(note that I switched to double quotes and included single quotes around the input parameter). BUT THIS IS STILL WRONG as it leaves you extremely vulnerable to SQL Injection, which would allow an attacker to potentially download or modify your entire database. Take the time to read up and learn how to use prepared queries, which is both more secure and also would have prevented this bug in the first place.

Answer 2

Others have suggested you use parameterized queries. That's good advice, but they didn't show you what it looks like (not that it's hard to find examples).

Here's a quick example using PDO:

$Sql = 'SELECT * FROM `news` WHERE `category` = ? ORDER BY `id` DESC';
$stmt = $pdo->prepare($Sql);
$stmt->execute([ $Param['category' ]);

This executes the query as if you had put $Param['category'] into the SQL, but it does it completely safely, even if the value contains special characters.

You don't put quotes around the ? placeholder. It's implied that the query will treat it as a single scalar value, not a column name. In fact, you can use parameters only for values — not for column names, or expressions, or SQL keywords or anything else.

Writing code using query parameters is easier than not using parameters, because it makes your code easier to write and easier to read.

Answer 3

You have to put quotes around any string in the query if it is not a field in the table. Try this:

$Sql = "SELECT * FROM `news` WHERE `category` = '".$Param['category']."' ORDER BY `id` DESC";

As was mentioned if you are pulling in that string from a source where you have to word about code injection you should use prepared statements or PDO.