SQL syntax error, I just can't see it

Question

Here is my code:

<?php $con = mysql_connect("localhost","solidarity","password"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("database", $con); $sql="INSERT INTO show_reviews (username, date, content, show) VALUES (".addslashes($_POST[username]).",".addslashes($_POST[date]).",".addslashes($_POST[content]).",".addslashes($_POST[show]).")"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo "1 record added"; mysql_close($con); ?> 

So, I used fsprint, and now I just used w3schools code, and this is my output with two code snippets:

Error: you have an error in the SQL syntax; check the manual that matches the version of MySQL server for the correct syntax to use next to the show. VALUES (Solidarity, 17:02 - Wed, August 1, 2012, testing, kr1971) 'on line 1

I use very similar syntax for the comment system and don't have this problem. If this helps, I also tried on local sql server and remote uninstall.

Please help me: (.

Answer 1

Put the values ​​inside single quotes:

 $sql=" INSERT INTO show_reviews (username, date, content, show) VALUES ('".addslashes($_POST[username])."','".addslashes($_POST[date])."','".addslashes($_POST[content])."','".addslashes($_POST[show])."')"; 

Also, as others have said, show is a reserved keyword in MySQL. You can see the full list of reserved keywords for MySQL 5.5 at http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

You can specify reserved words using the back side to be able to use them:

 INSERT INTO show_reviews (username, date, content, `show`) 

Quote IDs: http://dev.mysql.com/doc/refman/5.5/en/identifiers.html

Finally, summarize the comments about using addslashes() for escaping. I will let Chris Shiflett explain why this is bad: http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string

You really have to jump aboard prepared statements / parameterized queries using PDO or, at least, MySQLi. Here is an example of what the query looked like:

 $dbh = new PDO($connection_string); $sql = "INSERT INTO show_reviews (username, date, content, show) VALUES (?, ?, ?, ?)"; $stmt = $dbh->prepare($sql); $stmt->execute(array($_POST['username'], $_POST['date'], $_POST['content'], $_POST['show'] )); while ($row = $stmt->fetch()) { print_r($row); } 

This is just an example, it’s still good to disinfect your $ _POST variables and do everything possible so that the data you receive is exactly what you were trying to get. These prepared statements take care to elude you correctly and, if you use PDO, the right way for your particular database engine.

Answer 2

show is the mysql keyword. Therefore, the column name cannot be. You will need to avoid this if you want to use show as the column name.

Answer 3

show is a reserved keyword in SQL. You must enclose it with reverse windows in order to use it as the column name.

Answer 4

Please use this query

 $sql= 'INSERT INTO show_reviews (username, date, content, show) VALUES ("'.addslashes($_POST[username]).'",".'addslashes($_POST[date]).'","'.addslashes($_POST[content]).'","'.addslashes($_POST[show]).'")'; 

Answer 5

Your values ​​must be enclosed in quotation marks.

 $sql="INSERT INTO show_reviews (username, date, content, show) VALUES ('".addslashes($_POST[username])."','".addslashes($_POST[date])."','".addslashes($_POST[content])."','".addslashes($_POST[show])."')"; 

The reserved word is also shown, so you need to enclose it in backticks.

Answer 6

To clarify Sebastian’s comment, use PDO: is it more resistant (or immune?) To SQL injection attacks. The code will look something like this:

 <?php try { $handle = new PDO('mysql:host=localhost;dbname=myDatabaseName', 'username','password'); $prepared = $handle->prepare("INSERT INTO show_reviews (username, date, content, show) VALUES (?,?,?,?)"); if($prepared->execute(array($_POST['username'], $_POST['date'], $_POST['content'], $_POST['show']))) { echo "1 record inserted..."; }else { echo "insert failed..."; } }catch(PDOException $ex) { // error connecting to database } ?>