I was reading about the second order MySQL injection on this page Are PDO prepared statements sufficient to prevent SQL injection?.
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,
I simply do
$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;
I found there are two different ways to set the charset in pdo
$pdo = new PDO("mysql:host=" . DB_HOST . ";charset=utf8;......);
and
$pdo->exec("set names utf8");
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...
But I had never set the charset in my codes (as shown in first code) so I have a few questions
- for the first code where I am not setting any charset, what would the Default charset, and would it be safe?
- is it related to the charset of the database, for my database charset (Collation) is
ut8_general_ci (found that in phpmyadmin->operations)?
- 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?
The option character_set_client is what MySQL uses for the character set of queries and data that the client sends.
The default is utf8 in MySQL 5.5, 5.6, and 5.7, and utf8mb4 in 8.0.
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.
Re your comment:
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.
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.
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.
