PHP mysql_real_escape_string()：用户'www-data'@'localhost'的访问被拒绝
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18378976/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP mysql_real_escape_string(): Access denied for user 'www-data'@'localhost'
提问by Tamere Jlanik
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 :
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 ?
我真的不知道发生了什么，因为我在本地开发 ubuntu 服务器上没有问题。Mysql, apache 和 php 版本是一样的。唯一的事情是我在 apache prod 服务器上使用虚拟主机。不知道发生了什么......我在 apache 或 php 配置之一中遗漏了什么吗？
EditHere 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
我的 apache 虚拟主机配置
<VirtualHost *:80>
# Admin email, Server Name (domain name), and any aliases
ServerAdmin [email protected]
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
编辑 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.
好的，问题是我在 mysql 服务器上没有任何 www-data 用户。所以我只是在 mysql 中添加了没有密码和特权的用户 www-data ，这工作正常。我将在未来尝试使用提到的 PDO 引用。感谢所有试图帮助我的人。
采纳答案by yves
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 twopeople didn't know what they were doing.
请注意，这个答案很糟糕，会让您面临安全漏洞。这是错误的解决方案。不要这样做。进一步查看问题的实际解决方案。这个答案被接受的事实只是意味着两个人不知道他们在做什么。
It's because you haven't a www-data in your mysql database!
这是因为你的 mysql 数据库中没有 www-data！
You can add the www-data user through phpmyadmin and give that user no privilege and it should work
您可以通过 phpmyadmin 添加 www-data 用户并且不给该用户任何权限，它应该可以工作
回答by deceze
You either use PDO or you use the mysql extension, don't use both at the same time. mysql_real_escape_stringis 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.
您要么使用 PDO，要么使用 mysql 扩展，不要同时使用两者。mysql_real_escape_string是mysql扩展的一个函数。它需要连接到数据库才能运行。调用它时，如果您之前没有使用 建立连接，它会尝试建立一个连接mysql_connect，从而模拟所需的登录凭据。在您的本地机器上，您显然没有密码保护，并且 MySQL 用户的帐户名与运行 Web 服务器的名称相同，因此幸运地工作正常。在生产系统上，凭据不同，无法建立连接。
Stop using mysql_real_escape_stringwith PDO. Either use PDO's string quoting functions or, better, use prepared and parameterized queries and bindyour values.
停止mysql_real_escape_string与 PDO 一起使用。要么使用 PDO 的字符串引用函数，要么更好地使用准备好的参数化查询和bind您的值。
回答by Oliver
mysql_real_escape_stringneeds a valid link identifier (returned by mysql_connect()), see http://php.net/manual/en/function.mysql-real-escape-string.php
mysql_real_escape_string需要一个有效的链接标识符（由 返回mysql_connect()），参见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 ifmysql_connect()was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.
link_identifier：MySQL 连接。如果未指定链接标识符，
mysql_connect()则假定为最后打开的链接。如果没有找到这样的链接，它会尝试创建一个，就像mysql_connect()没有参数调用一样。如果未找到或建立连接，则会生成 E_WARNING 级别错误。
Your connection is opened by PDO, so you haven't any valid link identifier for mysql_real_escape_string.
您的连接由 PDO 打开，因此您没有任何有效的mysql_real_escape_string.
Try to use PDO::quote
尝试使用 PDO::quote
回答by Maija Vilkina
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.
我在尝试修改 Wordpress 后端中的用户搜索时遇到了同样的问题，缺少的是正确的 db 连接（也不推荐使用 mysql_real_escape_string()）。全局变量在 wp-config.php 中定义。
This is the working function. Note the $con variable. More explanation available here.
这是工作功能。注意 $con 变量。此处提供更多解释。
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;
}
}
回答by Bhavin Jasani
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
这个问题通常在数据库连接设置为mysqli时出现，为了解决这个问题，即使设置了mysqli，也要使用mysql_real_escape_string()。只需转到 php.ini 并找到：mysql.default_user 参数并将值设置为您的默认用户，例如：mysql.default_user = root
and restart the apache, your problem is resolved now.. enjoy
并重新启动apache，您的问题现在已解决..享受
