PHP mysql_real_escape_string(): Access denied for

Question

I just uploaded my website on the production server and i get the error :

Warning: mysql_real_escape_string(): Access denied for user 'www-data'@'localhost' (using password: NO) in file.php on line 106

Warning: mysql_real_escape_string(): A link to the server could not be established in file.php on line 106

the code of the function is

include('./../inc/conn.php');
if(isset($_GET['query']))$q = clean($_GET['query']);
function clean($var){
    return(mysql_real_escape_string($var));
}   

the code of inc/conn.php :

try {
  $dns = 'mysql:host=localhost;dbname=mydatabase';
  $user = 'root';
  $pw = 'rootpw';

  $options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND    => "SET NAMES utf8",
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
  );

  $db = new PDO( $dns, $user, $pw, $options );
} catch ( Exception $e ) {
    echo "Connection error : ", $e->getMessage();
    die();
}

I really don't know what is going on since i have no problem on my local dev ubuntu server. Mysql, apache and php version are the same. The only thing is i use virtual host on the apache prod server. Don't know what is going on... Is there something i missed in one of the apache or php config ?

Edit Here are my folder's rights:

sudo ls -l /home/user/public/domain.com/www/
total 28
drwxrwxr-x 13 user www-data 4096 Aug 22 12:30 adodb5
drwxrwxr-x  2 user www-data 4096 Aug 22 12:30 ajax
drwxrwxr-x  2 user www-data 4096 Aug 22 12:31 css
drwxrwxr-x  9 user www-data 4096 Aug 22 12:33 gfx
drwxrwxr-x  2 user www-data 4096 Aug 22 12:33 inc
drwxrwxr-x  2 user www-data 4096 Aug 22 12:34 js

my apache virtual host config

<VirtualHost *:80>
  # Admin email, Server Name (domain name), and any aliases
  ServerAdmin [e-mail]
  ServerName  www.domain.com
  ServerAlias domain.com

  # Index file and Document Root (where the public files are located)
  DirectoryIndex index.html index.php
  DocumentRoot /home/user/public/domain.com/www
        <Directory />
                Options FollowSymLinks
                AllowOverride None
        </Directory>
        <Directory /home/user/public/domain.com/www>
                Options FollowSymLinks
                AllowOverride All
                Order allow,deny
                allow from all
        </Directory>
    ScriptAlias /cgi-bin/ /usr/lib/cgi-bin/
    <Directory "/usr/lib/cgi-bin">
            AllowOverride None
            Options +ExecCGI -MultiViews +SymLinksIfOwnerMatch
            Order allow,deny
            Allow from all
    </Directory>

  # Log file locations
  LogLevel warn
  ErrorLog  /home/user/public/domain.com/log/error.log
  CustomLog /home/user/public/domain.com/log/access.log combined
</VirtualHost>

Edit 2

Ok so the problem was i didn't have any www-data user on the mysql server. So i just added user www-data with no password and no privilege in mysql and this is working fine. I will in the future trying to use PDO quote as many mentionned. Thanks for everyone trying to help me.

Answer 1

I had the same problem when trying to modify user search in Wordpress backend and the thing lacking was a proper db connection (also mysql_real_escape_string() is deprecated). The globals are defined in wp-config.php.

This is the working function. Note the $con variable. More explanation available here.

if(is_admin()) {


add_action( 'pre_user_query', 'user_search_by_email' );
  function user_search_by_email($wp_user_query) {
    if(false === strpos($wp_user_query->query_where, '@') && !empty($_GET["s"])) {
      $con = mysqli_connect(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME);
      $wp_user_query->query_where = str_replace(
              "user_nicename LIKE '%".mysqli_real_escape_string($con, $_GET["s"])."%'",
              "user_nicename LIKE '%".mysqli_real_escape_string($con, $_GET["s"])."%' OR user_email LIKE '%".mysqli_real_escape_string($con, $_GET["s"])."%'",
              $wp_user_query->query_where);
      mysqli_close($con);

    }
    return $wp_user_query;
  }
}

Answer 2

This problem arise usually when database connections are set to mysqli, to resolve this problem and use mysql_real_escape_string() even if mysqli is set. Just go to php.ini and find: mysql.default_user parameter and set the value to your default user for example : mysql.default_user = root

and restart the apache, your problem is resolved now.. enjoy

Answer 3

You either use PDO or you use the mysql extension, don't use both at the same time. mysql_real_escape_string is a function of the mysql extension. It needs a connection to the database to function. When calling it, it tries to establish a connection if you didn't previously establish one using mysql_connect, guestimating the required login credentials. On your local machine, you apparently have no password protection and the account name for the MySQL user is the same as the name the web server runs under, so it happens to luckily work. On the production system the credentials are different and it can't establish a connection.

Stop using mysql_real_escape_string with PDO. Either use PDO's string quoting functions or, better, use prepared and parameterized queries and bind your values.

Answer 4

Note that this answer is terrible and opens you up to security vulnerabilities. It is the wrong solution. Do not do this. See further down for the actual solution to the problem. The fact that this answer got accepted just means that two people didn't know what they were doing.

It's because you haven't a www-data in your mysql database!

You can add the www-data user through phpmyadmin and give that user no privilege and it should work

Answer 5

mysql_real_escape_string needs a valid link identifier (returned by mysql_connect()), see http://php.net/manual/en/function.mysql-real-escape-string.php

link_identifier: The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.

Your connection is opened by PDO, so you haven't any valid link identifier for mysql_real_escape_string.

Try to use PDO::quote