Sql with mysql reserved word

Question

I have this following sql code:

$sql = "INSERT INTO data (Artist, Name) VALUES ('TF2', 'you're right behind me')";

The code itself looks normal but for some reason mysql doesn't want to allow me to save it. I get the following error:

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 're right behind me')' at line 1"

What I know the problem is because of the word "right" being reserved in mysql but I need to save it so how should my code look like. All help is appreciated

I believe that is because of extra single qoute `'you're right behind me'` in `you're`. Maybe you can escape it by using backslash? — JunM, Jul 23 '13 at 02:47
You need to escape your commas. Use `You\'re` for instance. There's a function for that, called `addslashes`. Consult the PHP manual on the matter http://php.net/manual/en/function.addslashes.php — Funk Forty Niner, Jul 23 '13 at 02:48
@Fred addaslashes is **NOT** the function that should be used to escape sql parameters. Each db library has it's own function that will take into account things such as the encoding of the connection to properly escape the string (mysql_escape_string, mysqli_real_escape_string, etc). Even better, would be using bound parameters so you don't have to escape them manually. — jcsanyi, Jul 23 '13 at 03:02
@jcsanyi You're right. I was 50% right, apostrophes need to be escaped, if manually inserted. ;-) — Funk Forty Niner, Jul 23 '13 at 03:03
I meant `mysql_real_escape_string` http://www.php.net/manual/en/function.mysql-real-escape-string.php - I looked up the wrong function. — Funk Forty Niner, Jul 23 '13 at 03:06
Okay, have used mysql_real_escape_string like two years ago, forgot about if that was the one. Thanks Fred and @jcsanyi — Amanda Wood, Jul 23 '13 at 03:11
@AmandaWood You're welcome, hope this solved your problem Amanda. — Funk Forty Niner, Jul 23 '13 at 03:12
@AmandaWood You haven't mentioned what libraries you are using to actually connect to mysql. If you're using mysql_connect, mysql_query, etc, then yes - mysql_escape_string() is what you want. If you're using mysqli or PDO, there's corresponding functions that you should use with them instead. — jcsanyi, Jul 23 '13 at 04:51
@AmandaWood If you **are** using the mysql_* functions, **[please reconsider](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).** They are no longer maintained and are officially deprecated. See the **[red box](http://php.net/manual/en/function.mysql-connect.php)**? You can use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) instead - [this page](http://php.net/manual/en/mysqlinfo.api.choosing.php) can help you decide which to use. — jcsanyi, Jul 23 '13 at 04:53

score 0 · Answer 1 · answered Jul 23 '13 at 02:49

0

Your problem is because you have an ' in the work you're. So your string is terminating to early in your sentence. Use you\'re instead to escape the character '

$sql = "INSERT INTO data ('Artist', 'Name') VALUES ('TF2', 'you\'re right behind me')";

answered Jul 23 '13 at 02:49

ObieMD5

2,684
1
16
26

score 0 · Accepted Answer · answered Jul 23 '13 at 02:50

0

As @Fred and @JunM have already commented, you have two issues. The first is that Name is a reserved word. The second is that you have a single quote inside your single quoted string. Change your SQL to this:

$sql = "INSERT INTO data (`Artist`, `Name`) VALUES ('TF2', 'you\'re right behind me')";

answered Jul 23 '13 at 02:50

Revent

2,091
2
18
33

1

This works for this particular exempel, but the thing is the name is submitted by user, and are different from time to time, is there any permament solution? – Amanda Wood Jul 23 '13 at 02:56
@AmandaWood http://php.net/manual/en/function.addslashes.php – Funk Forty Niner Jul 23 '13 at 02:59
@AmandaWood `addslashes()` is **not** what you should be using. See my comment above – jcsanyi Jul 23 '13 at 03:04
@AmandaWood He's right. I looked up the wrong function. See `jcsanyi's` comment above. – Funk Forty Niner Jul 23 '13 at 03:04
@jcsanyi I stand corrected. – Funk Forty Niner Jul 23 '13 at 03:04
@Fred Okay, should I be using mysql_escape_string? – Amanda Wood Jul 23 '13 at 03:06
@AmandaWood I would say yes, however I'm not an MySQL expert. Views on this anyone? There are examples on the PHP.net link I gave you. – Funk Forty Niner Jul 23 '13 at 03:10

score 0 · Answer 3 · answered Jul 23 '13 at 02:51

0

My experience with MySQL is limited, but I use SQL Server extensively. To me it seems that the problem is in the apostrophy used in the "you're right behind me". In SQL server, I'd have to use a double apostrophy, so the sql instruction would be something like this (notice the double apostrophy in the you''re):

$sql = "INSERT INTO data (Artist, Name) VALUES ('TF2', 'you''re right behind me')";

Hope this helps. Regards

answered Jul 23 '13 at 02:51

aplon

445
6
24

This is wrong, you have to escape ' with \' – ObieMD5 Jul 23 '13 at 02:51
Thanks for the clarification @ObieMD5. I use the double quotes in SQL Server. Regards – aplon Jul 23 '13 at 02:57
2

yet another argument for parameterized queries... escaping is done in a database specific way. – Orangepill Jul 23 '13 at 03:00

Sql with mysql reserved word

3 Answers3