The correct way to use mysql_query

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
Etherwood
Forum Newbie
Posts: 4
Joined: Tue Dec 01, 2009 3:10 pm

The correct way to use mysql_query

Post 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...
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: The correct way to use mysql_query

Post 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());
Etherwood
Forum Newbie
Posts: 4
Joined: Tue Dec 01, 2009 3:10 pm

Re: The correct way to use mysql_query

Post by Etherwood »

Is it necessary to do mysql_real_escape_string in PHP 5 ?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: The correct way to use mysql_query

Post 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.
Etherwood
Forum Newbie
Posts: 4
Joined: Tue Dec 01, 2009 3:10 pm

Re: The correct way to use mysql_query

Post by Etherwood »

All my variables run through FILTER_SANITIZE_STRING before they get to mysql.
This should be ok as it is?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: The correct way to use mysql_query

Post 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.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: The correct way to use mysql_query

Post 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
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply