CODEWITHSUNDEEP
phpmysql
Tony
Tony

Reputation: 298

Access denied for user 'root'@'localhost' (using password: NO)

I'm using a simple form to do a database query. The database is accessed via password which I've included in the code. I'm not sure why I keep hitting the error on the string escape and the undefined variable $query = htmlspecialchars($query);

<?php
$servername = "localhost";
$username = "xxx";
$password = "xxx";
$dbname = "xxx";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
}
?>

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Search</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <link rel="stylesheet" type="text/css" href="style.css"/>
</head>
<body>
    <form action="Search2.php" method="POST">
        <input type="text" name="query" />
        <input type="submit" value="Search" />
    </form>

<?php

if (isset($_POST['query'])) 
 $query = $_POST['query']; 

if (!empty($query)) 

    $query = $_POST['query']; 
    // gets value sent over search form


        $query = htmlspecialchars($query); 
        // changes characters used in html to their equivalents, for example: < to &gt;

        $query = mysql_real_escape_string($query);
        // makes sure nobody uses SQL injection

        $raw_results = mysql_query("SELECT LastName, FirstName FROM Staff
            WHERE (`LastName` LIKE '%".$query."%') OR (`FirstName` LIKE '%".$query."%')") or die(mysql_error());


        if(mysql_num_rows($raw_results) > 0){ // if one or more rows are returned do following

            while($results = mysql_fetch_array($raw_results)){
            // $results = mysql_fetch_array($raw_results) puts data from database into array, while it's valid it does the loop

                echo "<p><h3>".$results['LastName']."</h3>".$results['FirstName']."</p>";
                // posts results gotten from database(title and text) you can also show id ($results['id'])
            }

        }
        else{ // if there is no matching rows do following
            echo "No results";
        }


    ?>



</body>
</html>

Upvotes: 1

Views: 369

Answers (2)

Ohgodwhy
Ohgodwhy

Reputation: 50767

The issue here is that you've invoked a mysqli object with your credentials, however, later you try to execute with the mysql_ procedural method. You don't have a connection there. You need to stick with the mysqli object. Furthermore, you should use prepared statements to handle your user input on SQL queries.

Remove these, we don't need to do sanitization for prepared statements:

//BYE!
$query = htmlspecialchars($query); 
    // changes characters used in html to their equivalents, for example: < to &gt;

$query = mysql_real_escape_string($query);
    // makes sure nobody uses SQL injection

Now let's use the mysqli object and OOP prepared methods. However, first we need to construct our like statements as our query's variables aren't executed, you can't concatenate %?% directly into the prepared() statement.

$query = '%'.$query.'%';

$stmt = $mysqli->prepare("SELECT LastName, FirstName FROM Staff
        WHERE LastName LIKE ? OR FirstName LIKE ?");

Now we can bind the parameters to our $stmt object.

$stmt->bind_param('ss', $query, $query);

Let's execute it now and get our data back.

$result = $stmt->execute();

Then we can loop:

while ($row = $result->fetch_assoc()) {
    echo "<p><h3>".$result['LastName']."</h3>".$result['FirstName']."</p>";
}

Edit

You also don't need to escape your column names with a backtick because:

  • They don't have - in the name
  • They aren't reserved special words in MySQL.

Upvotes: 2

mbazli
mbazli

Reputation: 1

make sure your PHP version is below 7.0 as stated here:

http://php.net/manual/en/function.mysql-real-escape-string.php

Warning This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: mysqli_real_escape_string() PDO::quote()

Upvotes: 0

Related Questions