Page 1 of 1
MySQL Insert Statements Woes ' vs `
Posted: Wed Apr 30, 2008 4:38 am
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
Re: MySQL Insert Statements Woes ' vs `
Posted: Wed Apr 30, 2008 4:52 am
by onion2k
When you use single quotes, eg 'column', MySQL assumes that the thing between then is a string instead of a column name.
Re: MySQL Insert Statements Woes ' vs `
Posted: Wed Apr 30, 2008 12:53 pm
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'));";
Re: MySQL Insert Statements Woes ' vs `
Posted: Wed Apr 30, 2008 1:12 pm
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.
Re: MySQL Insert Statements Woes ' vs `
Posted: Fri May 02, 2008 1:32 pm
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.
Re: MySQL Insert Statements Woes ' vs `
Posted: Sat May 03, 2008 9:50 pm
by LonelyProgrammer
Believe it or not, this is something that they never taught at my IT diploma course on MySQL.
Thanks!
Re: MySQL Insert Statements Woes ' vs `
Posted: Sat May 03, 2008 10:18 pm
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.