How to escape reserved mysql words in query in php?

Question

So i figure i was going to change my discography table name from 'album' to 'release', coz it made more sense to my anyways... Big mistake! the queries stopped working suddenly... After researching for many hours, i realize that 'release' is a reserve word in mySQL and can be use only if the correct escaping is use. In this case the escaping is the symbol `, like this

<?php
$sql = 'SELECT release.release_id, release.title, release.subtitle
        FROM `release`
        WHERE release.released_id = 567';
?>

My question is if this is the correct way of escaping a reserved mysql word in PHP? Is there a normalize way of going about this? Thanks

Well, you have answered your own question really. ` is the way to do it. It is good practice to enclose table names thus, though personally, I find it messy and so simply avoid using reserved words, which solves the issue nicely. Remember that the name is for your benefit, not Mysql's as it sees 'release' as a binary string anyway. Perhaps a thesaurus? — Robert Seddon-Smith, Jul 13 '13 at 23:00
ok, thanks for the confirmation.. i wanna to make sure if this weird character was the right way on going about this... so if i want to avoid using the backticks everywhere i better use an alias on the table like SELECT rl.title FROM \`release\` as rl WHERE rl.release_id = 567 — Marco, Jul 13 '13 at 23:05

score 4 · Answer 1 · answered Jul 13 '13 at 22:58

4

Use backticks everywhere.

$sql = 'SELECT `release`.release_id, `release`.title, `release`.subtitle
        FROM `release`
        WHERE `release`.released_id = 567';

answered Jul 13 '13 at 22:58

Daniel A. White

187,200
47
362
445

score 2 · Accepted Answer · edited Sep 11 '18 at 12:10

2

to escape them just enclose them between two backticks. like this

$sql = 'SELECT `release`.release_id, `release`.title, `release`.subtitle
        FROM `release`
        WHERE `release`.released_id = 567';

EDIT:

to avoid make backticks everywhere just use an alias like that:

$sql = 'SELECT r.release_id, r.title, r.subtitle
        FROM `release` as r
        WHERE r.released_id = 567';

edited Sep 11 '18 at 12:10

Ramin Firooz

506
8
25

answered Jul 13 '13 at 22:59

echo_Me

37,078
5
58
78

so if i want to avoid using the backticks everywhere i better use an alias on the table like SELECT rl.title FROM \`release\` as rl WHERE rl.release_id = 567 – Marco Jul 13 '13 at 23:08
1

@Marco look my edited answer , yes use an aliace – echo_Me Jul 13 '13 at 23:16

How to escape reserved mysql words in query in php?

2 Answers2

Linked