Is SQL injection possible with mysqli_real_escape_string?

Question

Is there a way to exploit this code and login as some particular username (like Sam)? mysqli_real_escape_string() function escapes all NUL (ASCII 0), \n, \r, \, ', ", and Control-Z characters.

I tried with username = "Sam" and domain = "' union (SELECT 1, 123456) # a" but it doesn't work..

$user   = $_POST['user'];
$domain = $_POST['domain'];
$pwd    = $_POST['pwd'];

function login($username, $domain, $password) {
    global $vuln_db;
    $starttime = microtime(true);
    $username = mysqli_real_escape_string($vuln_db, trim($username));
    $domain   = mysqli_real_escape_string($vuln_db, trim($domain));
    $password = trim($password);
    if (empty($password) || empty($username) || empty($domain)) {
        return FALSE;
    }
    // We store the password in plaintext to keep the homework's code short.
    // For anything even remotely real, use a proper password storage scheme.
    $query = "SELECT user_id, password FROM users WHERE username = '$username' AND domain LIKE '$domain'";
    $result = mysqli_query($vuln_db, $query) or die(mysqli_error($vuln_db));
    if($result) {
        $row = mysqli_fetch_row($result);
        if($row) {
            $the_password = trim($row[1]);
            for($i = 0; $i < strlen($the_password); $i++) {
                /* Bruteforce is not the way! */
                usleep(100000);
                if($password[$i] != $the_password[$i]) {
                    $endtime = microtime(true);
                    return FALSE;
                }
            }
            return TRUE;
        } else {
            return FALSE;
        }
    }
}

Can I get true from this function with a SQL injection or other kind of techniques?

Possible duplicate of [SQL injection that gets around mysql\_real\_escape\_string()](https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) — Nigel Ren, May 29 '18 at 10:42
A best practice is to use a [prepared statement](https://secure.php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead of escaping every parameter, doing so it's easier to secure your queries. But you are using quotes around your strings so your variables should be safe after being escaped. — Anthony, May 29 '18 at 10:52
This is a sql injection exercise for the university, but I cannot find a way to log in.. Can the global $vuln_db variable be exploited in some way? — keyblade95, May 29 '18 at 12:20
@NigelRen I read that topic before opening this new one, but my case is quite different from that. I have single quotes between the variable and it is trimmed — keyblade95, May 29 '18 at 12:27

score 0 · Answer 1 · answered May 29 '18 at 17:14

The specific case you show is secure with mysqli_real_escape_string(). Unless your character set is gbk or sjis, which I can't tell because I don't know how you connected to the database.

Escaping has a number of edge cases where it doesn't work, as described in answers to SQL injection that gets around mysql_real_escape_string(). It takes careful thinking and testing to make sure you're not implementing code that is vulnerable in one of these edge cases. This thinking and testing makes it take longer to write code.

That's why the current best practice is to use query parameters instead of escaping strings.

$query = "SELECT user_id, password FROM users WHERE username = ? AND domain LIKE ?";
$stmt = mysqli_prepare($vuln_db, $query) or die(mysqli_error($vuln_db));
$stmt->bind_param('ss', $username, $domain);
$stmt->execute() or die(mysqli_error($vuln_db));
$result = $stmt->get_result() or die(mysqli_error($vuln_db));

Query parameters make it easier to write code, and easier to read the code
Query parameters don't have the edge cases of vulnerability that string-escaping does

P.S.: This is unrelated to your question about escaping, but you should also learn to use password_hash() and password_verify(), instead of storing passwords in plain text.

Is SQL injection possible with mysqli_real_escape_string?

1 Answers1