PHP-MySQL

1. PHP-MySQL By Jonathan Foss

2. PHP and MySQL Client Server Apache Web Browser PHP file MySQL PHP Recall the PHP architecture PHP can communicate with a MySQL server (either local or external)

3. PHP and MySQL • To start a connection use mysql_connect(): mysql_connect(“localhost”,”root”,”password”) or die(“Error connecting to mysql”); • Then select a database using mysql_select_db() mysql_select_db(“mydatabase”); • To perform a query use mysql_query(): $query = “SELECT id, email FROM users where name=\”Jonny\””; $res = mysql_query($query);

4. MySQL Results • mysql_query() returns results in a result structure $query = “SELECT id, email FROM users where name=\”Jonny\””; $res = mysql_query($query); • Need to fetch rows from the result before use • We can use either mysql_fetch_row() or mysql_fetch_assoc() • $row = mysql_fetch_row($res); • $row = mysql_fetch_assoc($res); $row[0] = “1” $row[1] = “jonny@dcs.warwick.ac.uk” $row[“id”] = “1” $row[“email”] = “jonny@dcs.warwick.ac.uk”

5. MySQL Security Issues • Many security issues inherited from external sources • Database security particularly important when reading user input • SQL Injection attacks common attacks with PHP $res = mysql_query("SELECT userid FROM teacher where name=\”$username\” AND password=\”$password\”"); But if $password is “ OR “1” = “1 The query becomes: SELECT userid FROM teacher where name=“name” AND password=“” OR “1” = “1” and the attacker will gain entry anyway! This is known as a SQL Injection attack!

6. Ways of securing PHP/SQL • One of the easiest ways of preventing SQL Injection attacks is to escape the string first. This can be done using the mysql_real_escape_string function. This adds a \ to all quotes, so the parameter can’t interfere with the query. $user = $_POST[“username”]; $password = $_POST[“password”]; $user = mysql_real_escape_string($user); $password = mysql_real_escape_string($password); $res = mysql_query(“SELECT userid FROM users WHERE name=“$user” and password=“$password”); So “ OR “1” = “1 becomes \” OR \”1\” = \” 1 And the password is treated as a single string rather than MySQL syntax

7. Securing PHP with MYSQLi • A better way is to use MySQLi, which is an improved version of the PHP MySQL driver • MySQLi is still fairly new, so some hosts may not provide it yet • MySQLi allows MySQL querys to be paramaterized. The queries are prepared without the parameters, and then inserted using the bind_param function • The first parameter of the bind_param describes the number and type of arguments. For instance “si” tells the function to expect two arguments: a string and an integer

8. PHP and MySQLi $user = “me”; $password=“password”; $query->prepare(“SELECT userid from users where user=? and password=?); $query->bind_param(“ss”,$user,$password); $query->bind_result($userid); $query->execute(); $query->fetch(); User id is now stored in $userid

9. MD5 Security • When storing passwords in a database, it is a good idea to hash them first • This prevents people who may have access to the database from discovering the user’s password • One of the most common hashes is MD5 (Message Digest) • When user registers, the registration script takes an md5 hash of the password and stores the hash instead of the password • When user logs in, the login script takes an md5 hash of the supplied password and checks it against the hash stored in the database • If these two hashes are identical, the login script lets the person in

10. MD5 Example • There are functions in both PHP and MySQL which calculate MD5 – this example uses both to demonstrate both Registration script: $user = $_GET[“user”]; $password = $_GET[“password”]; mysql_query(“INSERT INTO user (user, password) VALUES (“$user”, md5(“$password”))”) or die(“Error: “ . mysql_error()); Login script: $user = $_GET[“user”]; $password = md5($_GET[“password”]); $res = “SELECT id FROM user WHERE user=“$user” AND password=“$password””;