Transcript PHP and MySQL course

PHP-MySQL
By Jonathan Foss
PHP and MySQL
Client
Web
Browser
Server
Apache
PHP file
PHP
Recall the PHP architecture
PHP can communicate with a MySQL server (either local or
external)
MySQL
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);
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[0] = “1”
$row[1] = “[email protected]”
• $row = mysql_fetch_assoc($res);
$row[“id”] = “1”
$row[“email”] = “[email protected]”
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!
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
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
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
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
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””;