Today was day 4 of the GCIH course being taught by Mark Baggett. Things are going great and I'm learning stuff, I today we discussed SQL injection and cross site scripting.
Regarding prevention, the training it states to perform input checking and disallow/block the user from entering symbols or specials characters such the apostrophe (') or the semi-colon (;) or the percent (%), etc.
This bothers me a bit, while input validation and checking really is key and probably the most important aspect to prevent SQL injection from affecting you, my problem is with the preventing the use of these symbols. If these symbols are blocked, your users can't use words like can't. Also, say you're using this sort of validation / blocking on passwords; then you just restricted the passwords that a person can use.
I do a bunch of coding in PHP, and when I was learning how to protect the websites I was writing, the material I used to stated to use string escaping and functions such as mysql_real_escape_string().
Mark mentioned parameterization, I didn't see this mentioned the course material. (although I didn't look closely).
I believe this is the best way to prevent your user's input from affecting your SQL query. For example, if you are going to ask a database to verify a username password, the old method would be to construct your query something alone the lines like:
SELECT * FROM databse.table WHERE username = '$USERNAME' and password = '$PASSWORD'
This is a classic query that can be injected if you pass the variables in this way, such as passing:
Username: Password:
This turnes the query into:
SELECT * FROM database.table WHERE username = '' OR uid = 1; -- #' and password = 'password';
Which would return the values for the user with id of 1, typically the administrative user; of course this is just an example. The idea of parameterization, also known as using bind-variables is that the user input is not sent to the database in the same place as the SQL statement. Instead you set your query up such as:
SELECT * FROM database.table WHERE username = ? and password = ?
Then you pass the variables in later with a bind-variable and execute statement. With this format, you can pass just about, if not all variables and the statement doesn't change because it's already set in the database.
In php, this would look like:
<?php
$query = "SELECT * FROM database.table WHERE username = ? and password = ?";
$results = array();
if ($sth = $dbh->prepare($query)) {
$sth->bind_param('ss', $_POST['username'], $_POST['password']);
$sth->bind_result($results);
$sth->execute();
while($sth->fetch()) {
$result_count++;
print var_dump($results) . "\n";
}
}
?>
This turns uses mysqli, other languages may do this similarly, but the important part is in the query, not in the php code.
The class is not a programming class, and Mark did mention this as a way to protect against SQL injection. My main concern with the instruction to filter input such as apostrophes is that this type of recommendation comes from security vendors, and looking for apostrophes in user inputs such as with a web application firewall will produce a lot of false positives. The biggest is the use of apostrophes to show possession or when used in a contraction.
Having your security software alert every time a user's post contains an apostrophe without checking for other artifacts of an injection attempt is going to flood your logs and make you miss a real attempt.
As for the class, I'm having fun and learning things. Mark is doing a great job of keeping things interesting and making things relevant by providing real world anecdotal accounts that the class can relate to. I learned how the buffer overflows actually happen in the processor, which I knew how they can over flow a buffer, but not how that could cause code to execute, now I do. I'm looking forward to Saturday for the capture the flag portion.
- Log in to post comments