Unknown column 'General' in 'where clause'

Question

I'm trying to make a specific page for my topic page for a web forum based off of my topics table in my database. The thing is, the where clause is reading in my $_GET['id'] as my column instead of topic_name. I'm kinda puzzled on how that's happening.

HERE's a snippet of my code:

$sql = "SELECT *
        FROM
            topics
        WHERE 
            topic_name= " . mysqli_real_escape_string($link,$_GET['id']);
echo $sql;
$result = mysqli_query($link, $sql);
echo mysqli_error($link);

@ceejayoz do you mean the echo of $sql? There's that:SELECT * FROM topics WHERE topic_name= General — Talha Ahmed, Mar 29 '18 at 02:39
@YourCommonSense I don't find it super clear. I'm kinda noobish at this though, just started working with php a week ago. I understand that it's reading the 'id' as the column but I don't understand how to get it to read topic_name as the column. — Talha Ahmed, Mar 29 '18 at 02:41
you say it's a snippet and that to me, means that you're probably trying to echo results elsewhere from a looped result set. We need to know the full and exact error, including the filename and the line number. That `?id` - `$_GET['id']` is coming from somewhere. — Funk Forty Niner, Mar 29 '18 at 02:47
@FunkFortyNiner That is the full and exact error. I gave you all the pertinent code for this error. I didn't want to post my whole project. That was the error I got from mysqli_error. That get is coming from the href from my homepage. I don't think that's the error tho, because 'General' is the name of the Topic. So those are matching. — Talha Ahmed, Mar 29 '18 at 02:50
Use prepared statement or enclose your topic_name value in `'` — Karlo Kokkak, Mar 29 '18 at 03:15
@YourCommonSense It was not clear to me if `$_GET['id']` would be a string or a numeric character. It's clearer now, but I didn't feel the info available supported the initial close (at the time). — ceejayoz, Mar 29 '18 at 12:40

score 3 · Answer 1 · edited Mar 29 '18 at 12:41

3

Different SQL parts have different meaning. For example, a part enclosed in quotes is treated as a string literal.

Unquoted parts are more ambiguous, and can be, depending on the context, an SQL keyword, an SQL operator, an identifier (a column or a table name) or a numeric literal. Given all that, you are supposed to format query parts properly. For example, a string literal must be enclosed in quotes and have all special characters escaped.

As you failed to do so, the 'General' word is taken for a column name, as WHERE topic_name= General is a legitimate (though quite pointless in this context) SQL statement that would look for the value stored in the General field, would it was present in the table. Alas, there is no such field, hence the error.

The most important part, when you are building your query dynamically, you must never format any data literals manually but use prepared statements instead.

$stmt = $link->prepare("SELECT * FROM topics WHERE topic_name= ?"); 
$stmt->bind_param("s", $_GET['id']);
$stmt->execute();
$result = $stmt->get_result();

Beside other benefits, it will eliminate even the possibility to get such an error in the future.

edited Mar 29 '18 at 12:41

ceejayoz

176,543
40
303
368

answered Mar 29 '18 at 02:57

Your Common Sense

156,878
40
214
345

Thanks for the information on prepared statements. I didn't even know that these were a thing. I'll try to incorporate this method of using sql into my code from now on. I personally hate ambiguity. – Talha Ahmed Mar 29 '18 at 03:11
As a further step, consider using [PDO](https://phpdelusions.net/pdo) instead of mysqli. It has MUCH cleaner API and many helper methods that are extremely useful. – Your Common Sense Mar 29 '18 at 03:16
6

@YourCommonSense If you've got a problem with me, feel free to @ me on Twitter - I see you're on there. Passive aggressive digs at my job title in answers/comments are a bit off. I've edited it out of your (good) answer. – ceejayoz Mar 29 '18 at 12:44
@YourCommonSense How do you get the result for a query that doesn't use SELECT? The get_result only works for SELECT queries. – Talha Ahmed Apr 01 '18 at 18:30
@TalhaAhmed `get_result()` works for any query that returns a result set. – Paul Spiegel May 17 '18 at 12:32

Unknown column 'General' in 'where clause'

1 Answers1