Why is this SQL query syntax incorrect?

Question

I'm trying to retrieve some video information from a MySQL database and searching for it via a $_GET["v"] request and a mysql query "seen below":

$video = $_GET['v'];
$query = "SELECT * FROM vid--data WHERE v = '".htmlspecialchars($video)."'";
$result = mysql_query($query);
if (!$result) {
die('Invalid query: ' . mysql_error());
}

This is the error I'm getting:

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '--data WHERE v = '5JxC0plA3kz'' at line 1

How is this error possible?

Read this http://dev.mysql.com/doc/refman/5.7/en/identifiers.html — Muhammad Muazzam, Dec 29 '15 at 09:30
why you don't use [mysqli](http://php.net/manual/de/book.mysqli.php) — , Dec 29 '15 at 09:48
If `--` was not allowed, how would he be able to create the table? Maybe put the tablename in backticks. — Daniel W., Dec 29 '15 at 09:54
Side note: the legacy mysql extension has been discouraged for several years and was finally removed from PHP some weeks ago. — Álvaro González, Dec 29 '15 at 10:03

score 2 · Answer 1 · answered Dec 29 '15 at 10:01

First of all, PHP and SQL are entirely different languages. This question is completely unrelated to PHP to begin with.

As about SQL syntax, the Language Structure chapter offers a complete overview but you're specifically violating what's explained at Schema Object Names:

An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.

... and:

 Permitted characters in unquoted identifiers:

    ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)

    Extended: U+0080 .. U+FFFF

... thus - is not allowed in an unquoted identifier. Just quote it:

The identifier quote character is the backtick (“`”):
mysql> SELECT * FROM `select` WHERE `select`.id > 100;

(Maybe next time you could use more practical names when naming stuff so don't need to quote them every time.)

Why is this SQL query syntax incorrect?

1 Answers1