MySQL Insert Statements Woes ' vs `

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
LonelyProgrammer
Forum Contributor
Posts: 108
Joined: Sun Oct 12, 2003 7:10 am

MySQL Insert Statements Woes ' vs `

Post by LonelyProgrammer »

Okay, so I have this two pieces of code snippets:


Exhibit 1:

Code: Select all

 
$sql = "INSERT INTO users (`userid` , `email`, `username`, `password`) VALUES (null, '$userid', '$userName', SHA1('$password'));";
 
and Exhibit 2

Code: Select all

 
$sql = "INSERT INTO users ('userid' , 'email', 'username', 'password') VALUES (null, '$userid', '$userName', SHA1('$password'));";
 
Exhibit 2 does not work. The only difference is the quote - the working one uses ` while the non-working one uses '.

What is going on? This is not the first time INSERT statements are driving me crazy
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: MySQL Insert Statements Woes ' vs `

Post by onion2k »

When you use single quotes, eg 'column', MySQL assumes that the thing between then is a string instead of a column name.
LonelyProgrammer
Forum Contributor
Posts: 108
Joined: Sun Oct 12, 2003 7:10 am

Re: MySQL Insert Statements Woes ' vs `

Post by LonelyProgrammer »

Hi thanks,

So just to clarify, this should be a valid SQL statement:

Code: Select all

 
$sql = "INSERT INTO users (userid , email, username, `password`) VALUES (null, '$userid', '$userName', SHA1('$password'));";
 
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: MySQL Insert Statements Woes ' vs `

Post by Zoxive »

LonelyProgrammer wrote:Hi thanks,

So just to clarify, this should be a valid SQL statement:

Code: Select all

 
$sql = "INSERT INTO users (userid , email, username, `password`) VALUES (null, '$userid', '$userName', SHA1('$password'));";
 
Yes, but I would recommend getting into the habit of using backquotes(`) around field names.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: MySQL Insert Statements Woes ' vs `

Post by RobertGonzalez »

Use backticks on table and database names too. It is a good habit since if for some odd reason Sun/MySQL decide to make new reserved column names in the future and your tables use those names now, your queries could break.

It is a good habit to use backticks always.
LonelyProgrammer
Forum Contributor
Posts: 108
Joined: Sun Oct 12, 2003 7:10 am

Re: MySQL Insert Statements Woes ' vs `

Post by LonelyProgrammer »

Believe it or not, this is something that they never taught at my IT diploma course on MySQL.

Thanks!
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: MySQL Insert Statements Woes ' vs `

Post by RobertGonzalez »

Have you ever heard of cPanel, the very popular web based server management application? It literally broke to pieces when MySQL upgraded to version 5 because of a heap of new reserved words that they threw in to the release.

It was so bad that you could not install cPanel on a server with MySQL 5 because it would just error out all over the place. It took the cPanel developers months to fix the issues. And every issue stemmed from the use of newly created reserved words in tables and columns that were not backtick escaped in their code.

Seems like such a simple little thing to do but could have deadly consequences to your app if not done.
Post Reply