one issue most databases will encounter is SQL injection and spoofing. this is when crackers will try to make a query using the database’s form field to do something malicious. it’s an easy problem to spot, but hard to prevent. there are no preventInjection() functions. we just need to look at what’s available to us and how we work them into our code.
here are a few things to consider:
- by ensuring that characters are escaped, you can be comfortable knowing that all user input is treated as a string, so that words like
ORare not treated as SQL operators. - when checking the validity of a query, there is no general rule for what should be accepted. if you are performing a
DELETE, you will not want users to be able to insert aLIKEor anOR. if you are performing anINSERT, a common word such as “where” needs to be allowed from a textarea. while escaping characters will handle most of this for us, we may need more flexibility in certain situations, so being able to specify illegal terms is important. - originally, i had thought to make the class work regardless of the database. i am beginning to think it may be better to create different files depending on the database. the primary reason for this is because of functions like
mysql_real_escape_string()andmysql_insert_id().
implementation
the function, clean(), takes two parameters: a string and an array of strings. The first is the text to modify, and the second is an optional parameter that contains a list of all words to remove. you can use it like this:
$cDB = new cDatabase("","","",""); $cleanvar = $cDB->clean($var, array(" like", " or"));
the returned string will be properly escaped, ignored if it is numeric, and will have the illegal phrases you specified removed.
how it works
the function loops through the array of illegal words, and removes them. it then checks for non numerical strings and escapes them properly.
function clean($text, $removals = array()) {
$conn = mysql_connect($this->host, $this->user, $this->password)
or die(mysql_error());
so now, imagine a login screen that requires a username and password. we would expect the user to be a string, something like “admin”. a matching query for that screen might look like this:
SELECT password FROM users WHERE username = �admin'
so what’d happen if i were to type in ” ?’ OR username like?c%’”, into the login field?
SELECT password FROM users WHERE username = �' OR username LIKE �c%'
this would return all passwords that match a username starting with ‘c’. Now that you see the flaw, you can use the clean() function above to prevent it.
