1 / 10

PHP-MySQL

PHP-MySQL. By Jonathan Foss. 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). PHP and MySQL. To start a connection use mysql_connect():

alvis
Download Presentation

PHP-MySQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

More Related