CODEWITHSUNDEEP
phpmysqlpdo
matt
matt

Reputation: 787

When to use PDO prepared queries. mysql_real_escape error

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

Upvotes: 2

Views: 184

Answers (2)

tadman
tadman

Reputation: 211560

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();

Upvotes: 4

newfurniturey
newfurniturey

Reputation: 38416

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).

Upvotes: 4

Related Questions