Transcript
Page 1: PHP-MySQL By Jonathan Foss. PHP and MySQL Server Web Browser Apache PHP file PHP MySQL Client Recall the PHP architecture PHP can communicate with a MySQL

PHP-MySQL

By Jonathan Foss

Page 2: PHP-MySQL By Jonathan Foss. PHP and MySQL Server Web Browser Apache PHP file PHP MySQL Client Recall the PHP architecture PHP can communicate with a MySQL

PHP and MySQL

Server

Web Browser

Apache

PHP file

PHP

MySQL

Client

Recall the PHP architecturePHP can communicate with a MySQL server (either local or external)

Page 3: PHP-MySQL By Jonathan Foss. PHP and MySQL Server Web Browser Apache PHP file PHP MySQL Client Recall the PHP architecture PHP can communicate with a 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);

Page 4: PHP-MySQL By Jonathan Foss. PHP and MySQL Server Web Browser Apache PHP file PHP MySQL Client Recall the PHP architecture PHP can communicate with a MySQL

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] = “[email protected]”$row[0] = “1”$row[1] = “[email protected]

$row[“id”] = “1”$row[“email”] = “[email protected]”$row[“id”] = “1”$row[“email”] = “[email protected]

Page 5: PHP-MySQL By Jonathan Foss. PHP and MySQL Server Web Browser Apache PHP file PHP MySQL Client Recall the PHP architecture PHP can communicate with a MySQL

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” = “1The 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!

Page 6: PHP-MySQL By Jonathan Foss. PHP and MySQL Server Web Browser Apache PHP file PHP MySQL Client Recall the PHP architecture PHP can communicate with a MySQL

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\” = \” 1And the password is treated as a single string rather than MySQL syntax

Page 7: PHP-MySQL By Jonathan Foss. PHP and MySQL Server Web Browser Apache PHP file PHP MySQL Client Recall the PHP architecture PHP can communicate with a MySQL

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

Page 8: PHP-MySQL By Jonathan Foss. PHP and MySQL Server Web Browser Apache PHP file PHP MySQL Client Recall the PHP architecture PHP can communicate with a MySQL

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 $useridUser id is now stored in $userid

Page 9: PHP-MySQL By Jonathan Foss. PHP and MySQL Server Web Browser Apache PHP file PHP MySQL Client Recall the PHP architecture PHP can communicate with a MySQL

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

Page 10: PHP-MySQL By Jonathan Foss. PHP and MySQL Server Web Browser Apache PHP file PHP MySQL Client Recall the PHP architecture PHP can communicate with a MySQL

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””;


Top Related