You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

Question

I am doing a form with php/html and I want to insert details into my database using

$sql = "INSERT INTO membre (Name, Age) VALUES ('$Name', '$Age')";

I think there is something missing in my SQL code. How to solve it?

Name is a reserved word so perhaps surround the field names with backticks — Professor Abronsius, Jul 31 '15 at 12:13
[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Jul 31 '15 at 12:13
yeah, we need to see the full error here. probably just need to escape the values and being halted by a character that MySQL is complaining about — Funk Forty Niner, Jul 31 '15 at 12:14
We'll need to see the full SQL statement too - what are the values for `$Name` and `$Age`? — halfer, Jul 31 '15 at 12:28

score 3 · Accepted Answer · edited May 23 '17 at 12:21

First of all, you're using a MySQL reserved word as a column name (Name). It is OK to do that as long as you surround your columns names in back ticks in your query:

$sql = "INSERT INTO membre (`Name`, `Age`) VALUES ('$Name', '$Age')";

If you're not preparing your queries it is quite possible that you'll run into errors because of names with apostrophes in them, such as "O'Shaughnessy". In this case, when not using prepared statement, you must make sure to escape all of your input in order to handle apostrophes properly. this would make the above input "O\'Shaughnessy". You can do this with one of the string escape functions PHP provides.

You're also at risk for SQL Injection Attacks. if you're not using prepared statements for your queries or properly cleansing data input by users.

Second, I am going to go out on a limb here and guess that you are using an older, deprecated database API, the mysql_* functions. If you can, you should stop using mysql_* functions. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and consider using PDO, it's really not hard.

score -1 · Answer 2 · answered Jul 31 '15 at 12:20

-1

First of all, when using MySQL as a database you want to put ` around the columns in your queries. This is good practice because then you tell MySQL that it should not be interpreted as keywords. Keywords such as name and datetime. So your query should look like this instead:

INSERT INTO membre (`Name`, `Age`) VALUES ('$Name', '$Age')

Also, when you are working with strings in PHP it's easy to miss ' and stuff. So I recommend you to make your SQL string look like this instead

$sql = "INSERT INTO membre (Name, Age) VALUES ('".$Name."', '".$Age."')";

Also, I think that what's causing you the most trouble is that membe is misspelled. I think what you mean is member. So, change your query to

$sql = "INSERT INTO member (`Name`, `Age`) VALUES ('".$Name."', '".$Age."')";

And it should work.

answered Jul 31 '15 at 12:20

Petter Pettersson

377
7
20

1

*"Also, I think that what's causing you the most trouble is that membe is misspelled. I think what you mean is member."* - that wouldn't cause a syntax error but should throw something entirely different like "Table not found". – Funk Forty Niner Jul 31 '15 at 12:25
That might be true. Been a while since I worked with PHP & MySQL. – Petter Pettersson Jul 31 '15 at 12:27
No need to concatenate variables here. – Jay Blanchard Jul 31 '15 at 12:27
1

I've my money on something else, being unescaped input. – Funk Forty Niner Jul 31 '15 at 12:28
Considering Name is a reserved word in MySQL that seems likely. – Petter Pettersson Jul 31 '15 at 12:29
sometimes, it's a combination of. I for one and on my system, `name` doesn't need escaping and TBH, never did find out the "why", strangely enough. – Funk Forty Niner Jul 31 '15 at 12:31

score -1 · Answer 3 · edited Jul 31 '15 at 12:23

-1

You can use escape string function "addslashes" while inserting or updating the records.

$sql = "INSERT INTO membre (`Name`, `Age`) VALUES ('" . addslashes($Name) . "', '$Age')";

edited Jul 31 '15 at 12:23

Saty

22,443
7
33
51

answered Jul 31 '15 at 12:22

Krishnadas V R

1
1

score -2 · Answer 4 · answered Jul 31 '15 at 12:15

-2

Try this

$sql = "INSERT INTO membre (Name, Age) VALUES (\'$Name\', \'$Age\')";
OR
$sql = "INSERT INTO membre (Name, Age) VALUES ('".$Name."', '".$Age."')";

answered Jul 31 '15 at 12:15

Vincent

66
6

3

Why should the OP "try this"? A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO. – Jay Blanchard Jul 31 '15 at 12:15

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

4 Answers4