2014 database - course 3 - PHP and MySQL

50 %
50 %
Information about 2014 database - course 3 - PHP and MySQL

Published on March 12, 2014

Author: enskylin



PHP - MySQL Ensky / 林宏昱

Load data from database GET /enskylin HTTP/1.1 Host: HTTP/1.1 200 OK HTML

generate HTML GET /enskylin HTTP/1.1 Host: HTTP/1.1 200 OK HTML

How to access database? • today's topic :D

Establish a connection (you should set it up during your installation) (If you use cscc account, then follow the instruction on cscc MySQL website) username: root password: nctu5566 login successfully

Do some Queries Insert: Create Select: Read Update: Update Delete: Delete INSERT INTO users (id, pw) VALUES ('jlhuang', 'iLove5566') Query OK, 1 rows affected

Dealing with Results Generate the correspond HTML SELECT * FROM users 100 row in set (0.00 sec)

That's all.

Hello world! - connect Establish a connection: $db_host = "host_name"; $db_name = "database_name"; $db_user = "user_name"; $db_password = "password"; $dsn = "mysql:host=$db_host;dbname=$db_name"; $db = new PDO($dsn, $db_user, $db_password);

Hello world! - Insert SQL -- INSERT INTO `users` (id, username, gender) VALUES(1, 'Ensky', 'male') PHP -- $sql = "INSERT INTO `users` (id, username, gender)" . " VALUES(?, ?, ?)"; $sth = $db->prepare($sql); $sth->execute(array(1, 'ensky', 'male')); id username gender 1 Ensky male

Hello world! - Select $sql = "SELECT * FROM `users`" . " WHERE `username` = ? AND `password` = ?"; $sth = $db->prepare($sql); $sth->execute(array('ensky', 'nctu5566')); id username password gender 1 Ensky nctu5566 male 2 Emily sdfasdf female

Hello world! - Retrieve $sql = "SELECT username, gender FROM `users`" . " WHERE `username` = ? AND `password` = ?"; $sth = $db->prepare($sql); $sth->execute(array('ensky', 'nctu5566')); while ($result = $sth->fetchObject()) { echo $result->name . $result->gender; } // Ensky male // Emily female // … id username password gender 1 Ensky nctu5566 male 2 Emily sdfasdf female

Named parameters $sql = "SELECT username, gender FROM `users`" . " WHERE `username` = ? AND `password` = ?"; $sth = $db->prepare($sql); $sth->execute(array('ensky', 'nctu5566')); is equal to $sql = "SELECT username, gender FROM `users`" . " WHERE `username` = :un AND `password` = :pw"; $sth = $db->prepare($sql); $sth->execute(array( ':un' => 'ensky', ':pw' => 'nctu5566'));

PHP Data Objects • PDO is an OO style class • Classes – PDO • PDO __construct ( string $dsn, [, string $username [, string $password ]]) • PDOStatement prepare( string $statement ) • PDOStatement query( string $statement ) – PDOStatement • bool execute ([ array $input_parameters ] ) • mixed fetchObject ([ string $class_name = "stdClass" [, array $ctor_args ]] )

Don't use mysql_* • There are many libraries to help you connect to MySQL database – MySQL – MySQLi – PDO • If your books recommends you to use mysql_xxx functions, throws it.

Don't use mysql_* • What's the problem of mysql_ functions? – It is deprecated in PHP 5.5.0, and will be removed in PHP6 – SQL Injection problem • no prepared statement – Only support MySQL(PDO supports 12 different databases)

What's SQL injection?

Simple query(use mysql ext) login_action.php -- <?php mysql_connect($db_host, $db_user, $db_password); mysql_select_db($dn_name); $result = mysql_query( "SELECT * FROM `users`" ." WHERE `email` = '{$_POST['email']}'" ." AND `password = '{$_POST['password']}'" ); // …

Simple query(use mysql ext) login_form.php login_action.php -- $result = mysql_query( "SELECT * FROM `users`" ." WHERE `email` = '{$_POST['email']}'" ." AND `password = '{$_POST['password']}'" );

Simple query(use mysql ext) login_form.php login_action.php -- $result = mysql_query( "SELECT * FROM `users`" ." WHERE `email` = ''" ." AND `password = 'nctu5566'" );

Simple query(use mysql ext) $result = mysql_query( "SELECT * FROM `users`" ." WHERE `email` = ''" ." AND `password = 'nctu5566'" ); SELECT * FROM `users` WHERE `email` = '' AND `password` = 'nctu5566'

SQL injection "--" in SQL represents "comments" SELECT * FROM `users` -- I want to select all from user SELECT * FROM `users` -- today is a good day

SQL injection If a cracker knows your query logic: SELECT * FROM `users` WHERE `email` = 'user_account' AND `password = 'user_password' give a try: user_account = ' OR 1=1 -- SELECT * FROM `users` WHERE `email` = '' OR 1=1 --' AND `password = 'user_password' OOPS!

SQL injection SELECT * FROM `users` WHERE `email` = '' OR 1=1 --' AND `password = 'user_password' Since 1=1 is obviously true in any circumstances, and below messages are commented out, this instruction will select all users instead of logged in user.

Prepared statement • By prepare query statement before execute, we can prevent SQL injection PREPARE SELECT * FROM `user` WHERE `id`=? AND `password`=? OK, prepared EXECUTE "enskylin", "nctu5566" 1 row in set (0.00 sec)

Password Hashing • Let's look at User creation INSERT INTO (id, password) VALUES ('ensky', 'nctu5566') • Actually, it is very dangerous! • Note that Database server is able to be cracked If hackers can get your "real password", than it is a big problem • Even more, if database administrator can access your real password, than it should be a problem, too. more plaintext passwords:

How to solve the plaintext password problem? Password Hashing

Hashing! a many-to-one no inverse function Password Hashed PW hello 5d41402abc4 … world 7d793037a07 …

Flow • register • login • Reset hello 5d41402abc4 … 5d41402abc4 … generate hashed password save to database hello 5d41402abc4 … 5d41402abc4 … generate hashed password verify with database's hash world 7d793037a07 … 7d793037a07 … generate new hashed password save to database

Crack • One common crack method is "rainbow table" – detail algorithm: wiki • password hashing can be cracked by using predefined hash tables • However it can be prevented by using "random salt" for each password

Best practice • Best practice to deal with hashing is to hash with "random salt" • Save 1. generate a random salt 2. hashing password use this random salt 3. save "hashed password" with random salt to database • Verify 1. query hashed password with random salt by user 2. regenerate hashed password and verify with real data

PHP support • PHP 5.5 supports password_hash, password_verify functions to deal with password hashing problem • However, CSCC only provides PHP 5.3 so you should use crypt function instead • Since crypt is not easy enough to use, TA provided TA's version:

Usage // create a hash $hash = password_hash($_POST['password']); // verify a hash if (password_verify($_POST['password'], $hash)) { echo 'Password is valid!'; } else { echo 'Invalid password.'; }

References • PDO: • crypt: • plainpassword: • pdo-mysql-mysqli: mysqli-pdo-%E7%9A%84%E5%B7%AE%E7%95%B0/

Add a comment

Related presentations

Presentación que realice en el Evento Nacional de Gobierno Abierto, realizado los ...

In this presentation we will describe our experience developing with a highly dyna...

Presentation to the LITA Forum 7th November 2014 Albuquerque, NM

Un recorrido por los cambios que nos generará el wearabletech en el futuro

Um paralelo entre as novidades & mercado em Wearable Computing e Tecnologias Assis...

Microsoft finally joins the smartwatch and fitness tracker game by introducing the...

Related pages

MySQL :: MySQL Training from Oracle University

MySQL Training from Oracle University. ... Top 3 Reasons. ... MySQL for Database Administrators course is for DBAs and other database professionals who ...
Read more

PHP with MySQL Essential Training |

PHP with MySQL Essential ... and functions), understand how to connect PHP to a MySQL database, ... In this course, we'll review the basic PHP data ...
Read more

MySQL Tutorials |

9 MySQL courses · 564 video tutorials ... and functions), and discover how to connect to a MySQL database and develop a complete web ... MySQL, and PHP, ...
Read more


The world's most popular open source database; Downloads; Documentation ... Replication Monitoring & Visualization with MySQL Enterprise Monitor 3.2
Read more

MySQL Database Administration | MySQL | Database ...

MySQL Database Administration allows you to have better control of your database. ... The recommended courses below will take you from beginner to expert ...
Read more

GitHub - a1phanumeric/PHP-MySQL-Class: Simple MySQL class ...

... Simple MySQL class written in PHP, for interfacing with a MySQL database. PHP-MySQL ... As of December 2014 I decided to upload the PHP MySQL Class ...
Read more

IT Training Courses - SQL & Databases - Find Professional ...

... Find Microsoft SQL database training courses ... (3) ... PHP Programming & MySQL for Web Development Course Overview The PHP Programming & MySQL ...
Read more

Database Programming with mySQL and PHP - Articles ...

... little background on databases and want to do database programming in mySQL and PHP. ... 3 Content Management ; 244 CSS ... about Database Programming ...
Read more

PHP Tutorial: PHP/MySQL for Beginners | Udemy

Learn PHP online with MySQL and Start ... to use advanced database operations with PHP and MySQL; ... need to complete this course. Lecture 3 ...
Read more