Preventing SQL Injection

1. Preventing SQL Injection ~example of SQL injection • $user = $_POST[‘user’];$pass = $_POST[‘pass’];$query = DELETE FROM Users WHERE user = ‘$user’ AND pass = ‘$pass’; • Someone enters anything’ or 1=1# • $user = $_POST[‘user’];$pass = $_POST[‘pass’];$query = DELETE FROM Users WHERE user = ‘anything’ OR 1=1#’ AND pass = ‘ ’; • the results you lose everything in your database ~ how to prevent injection • <?php$user = mysql_fix_string($_POST[‘user’]);$pass = mysql_fix_string($_POST[‘pass’]);$query = “SELECT * FROM Users WHERE user = ‘$user’ AND pass = ‘$pass’;function mysql_fix_string($string){ if (get_magic_quotes_gpc()) __ __$string = stripslashes(‘$string’); return mysql_real_escape_string($string);}?> • this function will remove any magic quotes added to a user in-putted string and then properly sanitize it for you • magic quotes are a built-in feature in php which automatically escape any characters such as a single and double quotes by prefacing them with a backslash (\)

2. Using Placeholders • Idea is to predefine a query using ? Characters where the data appears • Then instead of calling a MySQL query directly, you call the predefined one • This ensures that every item of data entered is inserted directly into the database and cannot be interpreted as SQL queries.\ • Once you have prepared a statement you can use it as often as you wish until you deallocate it. ~using placeholders with PHP • <?phprequire ‘login.php’;$db_server = mysql_connect($hostname, $username, $password);if ( !@db_server) die (“Unable to Connect to MYSQL” . Mysql_error());mysql_select_db($database) or die(“Unable to select database” . Mysql_error());$query = ‘PREPARE statement FROM “INSERT INTO classics VALUES (?,?,?,?,?)” ’;mysql_query($query);$query = ‘SET @author = “Emily Bronte”,’ . ‘@title = “Wuthering Height”,’ . ‘@category = “Classic Fiction”, ‘. ‘@year = “1847” ,‘. ‘@isbn = “9848483930202”,’ ;mysql_query($query);$query = ‘EXECUTE statement USING @author,@title,@category,@year,@isbn’;mysql_query($query);$query = ‘DEALLOCATE PREPARE statement’;mysql_query($query);?>

3. Preventing HTML Injection • occurs when you allow HTML to be input by a user and then displayed back by your website • one of the most common threat in HTML injection is that a malicious user will write the code that steals cookies from your site’s users • prevent this by simply calling the htmlentities function, which strips out all HTML markup codes and replaces with a form that displays the characters not allowing a browser to act on them. ~ example for preventing both SQL and XSS injections • <?php$user = mysql_entities_fix_string($_POST[‘user’])$pass = mysql_entities_fix_string($_POST[‘pass’])$query = “SELECT FROM users WHERE user=‘$user’ And pass=‘pass’”;function mysql_entities_fix_string($string){return htmlentities(mysql_fix_string($string));} function mysql_fix_string($string){if (get_magic_quotes_gpc()) string=stripslashes($string);return mysql_real_escape_string($string);}?>