Page 1 of 1

The correct way to use mysql_query

Posted: Tue Dec 01, 2009 3:26 pm
by Etherwood
Whats the correct way to use a mysql query?
I have seem many different ways of doing this.

At the moment I am using this...

Code: Select all

 
mysql_query("SELECT * FROM [color=#FF0000]user[/color] WHERE username='$username'") or die(mysql_error());
 
1. Should user have encased within ` ` ?

2. Whats the correct way to use a variable within a query?
a. username='$username'
b. username={$username}
c. username=" '.$username.' "
d. username=".$username."
e. Other...

Re: The correct way to use mysql_query

Posted: Tue Dec 01, 2009 3:30 pm
by John Cartwright
1. You should always encase your column, table, and aliases with the backtick to avoid any conflicts with reserved words.
2. They are all valid, and is completely up to your preference. I usually do

Code: Select all

$sql = "
   SELECT *
   FROM `foobar`
   WHERE foo = '". mysql_real_escape_string($foobar) ."'
   LIMIT 1
";
$result = mysql_query($sql) or die(mysql_error());

Re: The correct way to use mysql_query

Posted: Tue Dec 01, 2009 3:38 pm
by Etherwood
Is it necessary to do mysql_real_escape_string in PHP 5 ?

Re: The correct way to use mysql_query

Posted: Tue Dec 01, 2009 3:41 pm
by John Cartwright
Etherwood wrote:Is it necessary to do mysql_real_escape_string in PHP 5 ?
Neccesary, no. Absolutely recommended you ALWAYS validate and escape input into queries, yes.

Re: The correct way to use mysql_query

Posted: Tue Dec 01, 2009 4:16 pm
by Etherwood
All my variables run through FILTER_SANITIZE_STRING before they get to mysql.
This should be ok as it is?

Re: The correct way to use mysql_query

Posted: Tue Dec 01, 2009 4:30 pm
by John Cartwright
To guarantee the string is safe for mysql, you should always use mysql_real_escape_string() as there are a few characters that would not be filtered using the FILTER_SANITIZE_STRING filter. Secondly, filtering is not the same as escaping. Defense in depth dictates you should apply layers of security, escaping being one of them.

Re: The correct way to use mysql_query

Posted: Tue Dec 01, 2009 4:36 pm
by AbraCadaver
2. If the field type is not numeric you should use the single quotes regardless of how you embed the var in the query.

-Shawn