Mysql PHP Access denied for user ''@'localhost' to database

Question

Using the following code, the database can be connected to successfully. However, when trying to execute a query using the function 'executeNonQuery' I get the error { Access denied for user ''@'localhost' to database 'photobook' }. The user has full privileges, although strangely the query 'show grants' will not display that fact. However, when looking at user privileges in the GUI of mysql workbench it is. What can be the problem? I even used root, but the query is denied with the same error. The user can connect, but can't issue a query.

file: class.database.php

<?php
    class Database {
        function __construct(){
        }

        public function connect($host, $user, $pass){
            $dbcnct = mysqli_connect($host, $user, $pass);
            if(!$dbcnct){
                die("Couldn't Connect: " . mysql_error());
            }else{
                echo "Connected Successfully";
            }          
        }

        public function disconnect(){
            mysql_close($dbcnct);
            if(!$dbcnct){
                echo "Disconnected Successfully";
            }
        }

        public function executeNonQuery($database, $sql){
            mysql_select_db($database);
            $retval = mysql_query($sql, $dbcnct);
            if(!$retval){
                die("Couldn't Update Data: " . mysql_error());
            }
            echo "Update Successful";
        }
    }
?>


<?php
 include 'class.database.php';
 $db = new Database;
 $db->connect("localhost", "user", "pass"); 
 $userdata=$_GET['data'];
 $index=$_GET['index'];
 echo $index . "<br/>";
 foreach($userdata as $data){
     $sql = "update photobook set photoName='" . $data . "' where photoPosition=" . $index;

    $db->executeNonQuery("photoBook", $sql);
     echo $data . "<br/>";
 }
 $db->disconnect();

?>

As an aside, stop using mysql_* functions they are deprecated. Use MySQLi or PDO instead. Also consider using prepared statements. — Bart Friederichs
– Bart Friederichs, Commented Mar 5, 2014 at 7:44
Your class is all wrong you're using mysqli_connect and then mysql_* library — Prix
– Prix, Commented Mar 5, 2014 at 7:44
Denying access to an anonymous user seems like a good thing to me! Probably the most frequently forgotten security step! — Strawberry
– Strawberry, Commented Mar 5, 2014 at 8:21

Liam J · Accepted Answer · 2014-03-05 07:47:24Z

2

Your $dbcnct variable is only defined for the function where you use it, not the whole class. You need to use class variables so you can share variables throughout the different functions :

<?php
    class Database 
    {
        public $dbcnct;

        function __construct()
        {
            // Nothing
        }

        public function connect($host, $user, $pass){
            $this->dbcnct = mysqli_connect($host, $user, $pass);
            if(!$this->dbcnct){
                die("Couldn't Connect: " . mysql_error());
            }else{
                echo "Connected Successfully";
            }          
        }

        public function disconnect(){
            mysql_close($this->dbcnct);
            if(!$this->dbcnct){
                echo "Disconnected Successfully";
            }
        }

        public function executeNonQuery($database, $sql){
            mysql_select_db($database);
            $retval = mysql_query($sql, $this->dbcnct);
            if(!$retval){
                die("Couldn't Update Data: " . mysql_error());
            }
            echo "Update Successful";
        }
    }
?>

Now all you need to do is :

$database->connect("host", "user", "pass");
$database->executeNonQuery("database", "sql");

But first of all you need to fix the mysql_ and mysqli_ mixups :)

answered Mar 5, 2014 at 7:47

Liam J

1633 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2403940 Over a year ago

Thank you. I've spent hours on this. I should have asked earlier.

sreenivas · Accepted Answer · 2014-03-05 07:42:35Z

0

Try this: Adding users to MySQL

You need grant privileges to the user if you want external acess to database(ie. web pages).

http://dev.mysql.com/doc/refman/5.1/en/adding-users.html

CREATE USER 'a_project'@'%' IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON a_database.* TO 'a_project'@'%';

answered Mar 5, 2014 at 7:42

sreenivas

3951 gold badge3 silver badges18 bronze badges

Comments

Roscoe · Accepted Answer · 2014-03-05 07:55:14Z

0

i suggest you keeping $dbcnct as a variable in the database class so that when you call function executeNonQuery,the mysql connection won't be lost.

answered Mar 5, 2014 at 7:55

Roscoe

192 bronze badges

Comments

Joseph Collins · Accepted Answer · 2014-03-05 07:59:13Z

0

Put your connect script in the __construct() section, this initialize connection to call new Database; and make available to all functions in the Database Class without request new connection every time you run a query.

answered Mar 5, 2014 at 7:59

Joseph Collins

4612 silver badges9 bronze badges

Collectives™ on Stack Overflow

Mysql PHP Access denied for user ''@'localhost' to database

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related