PHP pdo mysql的默认字符集是什么 [英] PHP What is the default charset for pdo mysql

问题描述

我正在此页面上阅读有关二阶MySQL注入的信息 PDO准备好的语句是否足以防止SQL注入?.

I was reading about the second order MySQL injection on this page Are PDO prepared statements sufficient to prevent SQL injection?.

并带来了许多有关charset的问题，我不确定我的代码对MySQL注入是否安全

and it brought many questions about the charset, and I am not sure if my code is safe to MySQL injection

在我的代码中，我从未在进行查询时使用字符集，

In my code, I never use charset while making a query,

我只是做

$pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASSWORD, [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_PERSISTENT => false]);
$stmt = $pdo->prepare("SELECT * FROM keywords  WHERE keyword_name = ? || keyword_name = ?");
$stmt->execute(["hello","world"]);
rows = $stmt->fetchAll();
// show the data on webpage
$pdo = null;

我发现在pdo中set charset有两种不同的方法

I found there are two different ways to set the charset in pdo

$pdo = new PDO("mysql:host=" . DB_HOST . ";charset=utf8;......);

和

$pdo->exec("set names utf8");

根据此链接上的@ircmaxell答案已准备好PDO足以防止SQL注入的语句?.应该使用第一种方法来防止二阶SQL注入...

According to @ircmaxell answers on this link Are PDO prepared statements sufficient to prevent SQL injection?. the first method should be used to protect against second-order SQL injection...

但是我的代码中有never set charset(如第一个代码所示)，所以我有几个问题

But I had never set the charset in my codes (as shown in first code) so I have a few questions

1. 对于我未设置任何字符集的第一个代码，默认"字符集将是什么?它会安全吗?
2. 它与数据库的字符集有关吗，因为我的数据库字符集(排序规则)是ut8_general_ci(在phpmyadmin-> operations中找到)?
3. utf8字符集是否可安全用于第二次注入，即$pdo = new PDO("mysql:host=" . DB_HOST . ";charset=utf8;......);已针对所有类型的mysql注入完成工作?

1. for the first code where I am not setting any charset, what would the Default charset, and would it be safe?
2. is it related to the charset of the database, for my database charset (Collation) is ut8_general_ci (found that in phpmyadmin->operations)?
3. is utf8 charset in safe for second-order injection i.e $pdo = new PDO("mysql:host=" . DB_HOST . ";charset=utf8;......); is done job against all kind of mysql injections?

推荐答案

选项character_set_client是MySQL用于客户端发送的查询和数据的字符集的选项.

The option character_set_client is what MySQL uses for the character set of queries and data that the client sends.

在MySQL 5.5、5.6和5.7中默认为utf8，在8.0中为utf8mb4.

The default is utf8 in MySQL 5.5, 5.6, and 5.7, and utf8mb4 in 8.0.

也可以在您的my.cnf选项文件中全局更改，也可以通过

It can also be changed globally in your my.cnf options file, or per session by a SET NAMES statement.

连接时最好显式设置该选项，因此不必假定其默认值.

It's good to set the option explicitly when you connect, so you don't have to assume its default value.

发表您的评论

恐怕您会混淆两种不同的SQL注入情况.使用这五个特定的字符集时存在风险，但与二阶SQL注入无关.

I'm afraid you're confusing two different cases of SQL injection. There is a risk when using those specific five character sets, but it is not related to second-order SQL injection.

字符集风险归因于某些多字节字符集.插入反斜杠以转义文字引号字符是很常见的.但是在某些字符集中，反斜杠字节会合并到前一个字节中，从而形成一个多字节字符.这样一来，报价就无法转义了.

The character set risk is due to some multi-byte character sets. It's common to insert a backslash to escape a literal quote character. But in some character sets, the backslash byte gets merged into the preceding byte, forming a multi-byte character. That leaves the quote unescaped.

二阶SQL注入完全不同.它可以与任何字符集一起发生.这是攻击者通过合法手段(例如填写表格)将数据添加到数据库中的时候.插入数据的操作没有错误.但是它们插入的值包含旨在利用某些更高版本的SQL查询的语法.

Second-order SQL injection is totally different. It can occur with any character set. This is when an attacker adds data to your database through legitimate means, like filling out a form. Inserting the data is handled without error. But the values they insert contains syntax designed to exploit some later SQL query.

它依赖于开发人员，认为已经安全地保存到数据库中的数据以某种方式是安全的"，无需进行适当的参数化即可使用.

It relies on developers believing that data that has already been saved safely to their database is somehow "safe" for use without proper parameterization.

一个仅是偶然而不是恶意的二阶SQL注入示例可能是一个人的姓氏为"O'Reilly"，并且该名称被代码读取并在随后的查询中使用.

An example of second-order SQL injection that is merely accidental instead of malicious could be that a person has the last name "O'Reilly," and the name is read by the code and used in a subsequent query.

$name = $db->query("SELECT last_name FROM people WHERE id = 123")->fetchColumn();
$sql = "SELECT * FROM accounts WHERE account_owner_last_name = '$name'";

如果名称中包含单引号，则会使该示例中的第二个查询弄乱.

If the name contains a literal apostrophe, it would mess up the second query in that example.

这篇关于PHP pdo mysql的默认字符集是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！