PHP mysql_real_escape_string(): Access denied for user 'www-data'@'localhost'

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 contact@domain.com
  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.

You should have access to production server environment. Ask admin if you are privileged user. — user1502952, Aug 22 '13 at 11:24
@CathedralZealot I don't know it's the first time i configure a server myself.. I must create a new mysql user with less privileges ? — Tamere Jlanik, Aug 22 '13 at 11:28
@YogeshSuthar well i heard it's better to use this function to get ride of sql injection. Should i better use htmlspecialchars ? Thought if was only a php function but now i discovered it need mysql connection — Tamere Jlanik, Aug 22 '13 at 11:31
@CathedralZealot Ok i found i have to use `grant all on exampleDB.* to 'example_user' identified by 'pw';` and then use this on my connect script — Tamere Jlanik, Aug 22 '13 at 11:36
This requires a valid MySQL connection to be active. See [this](http://stackoverflow.com/a/4574677/1438393) answer. — Amal Murali, Aug 22 '13 at 11:36
@TamereJlanik PDO handles all sql injection thing when you use prepared statement, so need to use `mysql_real_escape_string`. — Yogesh Suthar, Aug 22 '13 at 11:38
@TamereJlanik I'm suspecting it's more of a folder permission issue. Are you able to change permissions on folders? — Cathedral Zealot, Aug 22 '13 at 11:41
@CathedralZealot yes i can. Altought i already added in the past www-data in the website folder — Tamere Jlanik, Aug 22 '13 at 11:50
@CathedralZealot updated my question so you can see folder rights nad my apache virtual host config — Tamere Jlanik, Aug 22 '13 at 11:57
You can find the solution for this problem [here][1] [1]: http://stackoverflow.com/a/22728123/1078570 — Jay, Mar 29 '14 at 07:11

score 11 · Answer 1 · edited May 23 '17 at 11:54

11

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.

edited May 23 '17 at 11:54

Community

1
1

answered Aug 22 '13 at 12:44

deceze

510,633
85
743
889

you are great. I have same problem, and adding a connection to mysql for `mysql_real_escape_string`solves my problem. just add `mysql_connect('localhost', 'mysql_user', 'mysql_password');` in some where your config file after normal connections. Or as i will do in near future convert all `mysql_real_escape_string´ to ´PDO::quote´ or `bind` – caglaror Sep 26 '14 at 07:33
2

Be aware that that is certainly **not a solution**! Don't use `mysql_real_escape_string` if you're using PDO! Period! – deceze Sep 26 '14 at 07:36

score 3 · Answer 2 · answered Aug 22 '13 at 12:30

3

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

answered Aug 22 '13 at 12:30

Oliver

66
1
3

Check your local username, does it exists as an user an your mysql dev server? Do you have an user called www-data with no password on your prod server? – Oliver Aug 22 '13 at 12:47
No it doesn't exist. I think that's why it aint working. What kind of privileges must that user have ? – Tamere Jlanik Aug 22 '13 at 13:04
It was the www-data user missing ! Now it's working. So it appears PDO use mysql_connect ? – Tamere Jlanik Aug 22 '13 at 13:22
PDO::quote() is an instance method and cannot be called statically. `Fatal error: Non-static method PDO::quote() cannot be called statically` – Stan Oct 13 '15 at 20:00

score 1 · Answer 3 · answered Feb 25 '15 at 17:59

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;
  }
}

score 1 · Answer 4 · answered Jul 21 '16 at 09:11

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

score -14 · Accepted Answer · edited Feb 24 '15 at 02:35

-14

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

edited Feb 24 '15 at 02:35

deceze

510,633
85
743
889

answered Sep 11 '13 at 13:56

yves

250
1
2
18

9

Worst possible answer. It understands nothing about the problem and promotes a terrible solution with negative security implications. And of course it got accepted. *facepalm* – deceze Nov 12 '13 at 14:40

PHP mysql_real_escape_string(): Access denied for user 'www-data'@'localhost'

5 Answers5

Linked