2

I'm been making a php site, developing on my local machine. Really new to this so this is the first thing i've ever attempted. When I moved to my host, i get the following error:

 Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied             for user 'matthew'@'localhost' (using password: NO)  on line 11

I've searched on here a fair bit and I'm pretty sure its because i need to 'prepare' my queries. What I am unsure of is when is it correct to prepare, and when not. I've added some of my queries below to explain in detail:

connection to db:

$hostname = "localhost";
$username = "root";
$password = "root";

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=wmpt", $username, $password);
//echo "Connected to database"; // check for connection
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }

Here is an example query:

$username = mysql_real_escape_string($_POST['run']);


$STH = $dbh->query("SELECT * FROM tblusers WHERE username = '$username' ");
$STH->setFetchMode(PDO::FETCH_ASSOC);  
$result = $STH->fetch();

My question is, do I only need to "prepare" a query if I am querying/inserting/updating the DB with user submitted data?

Is the above query bad practice? What if it didnt contain user submitted data, ie i wanted to query 

 $STH = $dbh->query("SELECT * FROM tblusers WHERE username LIKE '%hotmail%' ");

That probably a bad example, but I'm illustrating a developer defined query.

Is this the reason I get, and how i can avoid:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for     user 'matthew'@'localhost' (using password: NO)  on line 11
matt
  • 777
  • 2
  • 12
  • 25

2 Answers2

4

To address the error you're receiving, mysql_real_escape_string() requires an open connection via mysql_connect(). Because you don't have one, it's attempting to connect and failing (using a username of matthew, whereas your PDO is connecting with root). Additionally, you cannot (or, shouldn't) mix and match mysql_real_escape_string() and PDO - they're different libraries.

Regarding "when" to use prepared statements, the general rule of thumb is whenever values are not hardcoded. Your example of LIKE '%hotmail%' doesn't need to be prepared, it's hardcoded and never going to change (unless you manually update it, of course).

If you have a query that will take in a variable of any sort, be-it data from $_POST or $_GET, or a variable that a developer made 10 lines before the query, you should use a prepared statement (or at least escape it, check out PDO::quote).

newfurniturey
  • 37,556
  • 9
  • 94
  • 102
  • 1
    To be sure, you should escape *any* and *all* values substituted into your query. Even programmer-defined values can have unexpected issues. – tadman Oct 23 '12 at 19:45
  • @tadman Precisely; any variable that is not hardcoded should be escaped - either via a prepared statement or the very least an escaping-function. – newfurniturey Oct 23 '12 at 19:49
  • thanks for the detail on best practices - really really helpful – matt Oct 23 '12 at 20:44
4

If you're using PDO, you should not be using mysql_real_escape_string. The PDO library has a very robust SQL placeholder method that does a much better job.

$STH = $dbh->query("SELECT * FROM tblusers WHERE username = :username");
$STH->bindParam(':username', $username);
$STH->setFetchMode(PDO::FETCH_ASSOC);  
$result = $STH->fetch();
tadman
  • 208,517
  • 23
  • 234
  • 262