sql injection notes

SQL in Web Pages =
[
    SQL Injection =
    [
    /* 1 - '105 1=1' injection exploit */
        /* Allow web users to input their own search values. */
        txtUserId   = getRequestString("UserId");
        txtSQL      = "SELECT * FROM Users WHERE UserId = " + txtUserId;
        /* This is valid, but can be exploited when the user types certain terms such as '105 or 1=1' into the search box. */
        /* Inputting those values can possibly return all information from the Users table inside the database. */

    /* 2 - '=' exploit */
        /* User log in example with a username and password text field. */
        uName   = getRequestString("UserName");
        uPass   = getRequestString("UserPass");
        sql     = "SELECT * FROM Users WHERE Name = '" + uName + "' AND Pass = '" + uPass + "'";
        /* A hacker might be able to put a '=' into one of the text boxes and gain access to the names and passwords in the db. */
        SELECT * FROM Users WHERE Name = "" or ""="" AND Pass ="" or ""=""; /* This is what the server receives. */
        /* This will return all usernames and passwords directly from the Users table in the db. */

    /* 3 - Batched SQL statements */
        /* Using the same input code as the first example, typing "105; DROP TABLE Suppliers" causes the SQL batched statement to run. */
        /* A batched statement is multiple statements in a single request. Statements are seperated by a ; */
        SELECT * FROM Users; DROP TABLE Suppliers; /* This code returns all rows of the Users table, then deletes the Suppliers table. */
    ]
    Preventing SQL Injection =
    [
        /* Using Blacklist of words is common practice for preventing attacks, but this will cause certain words not to be able to be used
            such as 'delete' or 'drop'. Better alternative is to use SQL parameters. */
        SQL Parameters =
        [   /* Definition: Values that are added to an SQL query at execution time, in a controlled manner. This is the only proven practice. */
        /* Example 1 */
            txtUserId   = getRequestString("UserId");
            txtSQL      = "SELECT * FROM Users WHERE UserId = @0";
            db.Execute(txtSQL, txtUserId);
            /* The @ symbol used tells SQL to treat the entire input as a variable, even if a command is specified */
            /* For example, the input can be "DROP TABLE Suppliers" and it will only search for that entire string as a variable. Nothing executes */

        /* Example 2 */
            /* This shows the submission of multiple variables using SQL Parameters */
            txtNam  = getRequestString("CustomerName"); /* CustomerName is a value from input textfield */
            txtAdd  = getRequestString("Address");      /* Address is a value from input textfield */
            txtCit  = getRequestString("City");         /* City is a value from input textfield */
            txtSQL  = "INSERT INTO Customers (CustomerName, Address, City) Values(@0, @1, @2)";
            db.Execute(txtSQL, txtNam, txtAdd, txtCit);

            /* PHP version of the example above. The ':' works the same way as the '@' in javascript. */
            $stmt = $dbh->prepare("INSERT INTO Customers (CustomerName, Address, City) Values (:nam, :add, :cit)");
            $stmt->bindParam(':nam', $txtNam); /* $txtNam is value from input textfield */
            $stmt->bindParam(':val', $txtAdd); /* $txtAdd is value from input textfield */
            $stmt->bindParam(':cit', $txtCit); /* $txtCit is value from input textfield */
            $stmt->execute();
        ]
    ]
]