PHP with MySQL Database
PHP provides extensions for interfacing with MySQL database. The PHP's MySQL-related extensions include:
- the older
mysql
extension (not recommended), - the newer
mysqli
(mysql improved) extension, and - the object-oriented PHP Data Object (PDO).
The older mysql
extension provides a procedural interface and is intended for use with MySQL versions earlier than 4.1.3. It does not support all the latest MySQL features. The newer mysqli
extension (or mysql improved extension) supports features like prepared statement and transaction. It provides an object-oriented interface as well as a procedural interface. However, I recommend PHP Data Object (PDO) for new development, which is fully object-oriented with proper exception handling, having neater and simpler interface.
PHP Data Object (PDO) By Examples
Reference:
- PHP manual "PHP Data Objects (PDO)" @ http://www.php.net/manual/en/book.pdo.php.
Example 1: Create Database Connection, query() and exec()
This example is meant for toy programs. For production, use PDO prepared statement as illustrated in Example 2 to prevent SQL injection.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 |
<?php /** * Testing PDO MySQL Database Connection, query() and exec(). * For CREATE TABLE, INSERT, DELETE, UPDATE: * exec(): returns the affected rows. * For SELECT: * query(): returns a result set. */ // Define the MySQL database parameters. // Avoid global variables (which live longer than necessary) for sensitive data. $DB_HOST = 'localhost'; // MySQL server hostname $DB_PORT = '3306'; // MySQL server port number (default 3306) $DB_NAME = 'test'; // MySQL database name $DB_USER = 'testuser'; // MySQL username $DB_PASS = 'xxxx'; // password try { // Create a PDO database connection to MySQL server, in the following syntax: // new PDO('mysql:host=hostname;port=number;dbname=database', username, password) $dbConn = new PDO("mysql:host=$DB_HOST;port=$DB_PORT;dbname=$DB_NAME", $DB_USER, $DB_PASS); $dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Set error mode to exception echo 'Connected', '<br />'; // Run SQL statements // Use exec() to run a CREATE TABLE, DROP TABLE, INSERT, DELETE and UPDATE, // which returns the affected row count. $rowCount = $dbConn->exec('DROP TABLE IF EXISTS `test`'); echo 'DROP TABLE: ', $rowCount, ' rows', '<br />'; $rowCount = $dbConn->exec( 'CREATE TABLE IF NOT EXISTS `test` ( `id` INT AUTO_INCREMENT, `name` VARCHAR(20), PRIMARY KEY (`id`))'); echo 'CREATE TABLE: ', $rowCount, ' rows', '<br />'; $rowCount = $dbConn->exec("INSERT INTO `test` (`id`, `name`) VALUES (1001, 'peter')"); echo 'INSERT INTO: ', $rowCount, ' rows', '<br />'; // Use lastInsertId() to get the LAST_INSERT_ID of the AUTO_INCREMENT column. echo 'LAST_INSERT_ID (of the AUTO_INCREMENT column) is ', $dbConn->lastInsertId(), '<br />'; $rowCount = $dbConn->exec("INSERT INTO `test` (`name`) VALUES ('paul'),('patrick')"); echo 'INSERT INTO: ', $rowCount, ' rows', '<br />'; echo 'LAST_INSERT_ID (of the AUTO_INCREMENT column) is ', $dbConn->lastInsertId(), '<br /><br />'; // Use query() to run a SELECT, which returns a resultset. $sql = 'SELECT * FROM `test`'; $resultset = $dbConn->query($sql); // By default, resultset's row is an associative array // indexed by BOTH column-name AND column-number (starting at 0). foreach ($resultset as $row) { // Loop thru all rows in resultset echo 'Retrieve via column name: id=', $row['id'], ', name=', $row['name'], '<br />'; echo 'Retrieve via column number: id=', $row[0], ', name=', $row[1], '<br />'; print_r($row); // for showing the contents of resultset's row echo '<br />'; } echo '<br />'; // Run again with "FETCH_ASSOC" option. // Resultset's row is an associative array indexed by column-name only. $resultset = $dbConn->query($sql, PDO::FETCH_ASSOC); // print_r($resultset); // A PDOStatement Object foreach ($resultset as $row) { echo 'Retrieve via column name: id=', $row['id'], ', name=', $row['name'], '<br />'; print_r($row); // for showing the contents of resultset's row echo '<br />'; } echo '<br />'; // Run again with "FETCH_OBJ" option. // Resultset's row is an object with column-names as properties. $resultset = $dbConn->query($sql, PDO::FETCH_OBJ); foreach ($resultset as $row) { echo 'Retrieve via column name: id=', $row->id, ', name=', $row->name, '<br />'; print_r($row); // for showing the contents of resultset's row echo '<br />'; } // Close the database connection (optional). $dbConn = NULL; } catch (PDOException $e) { $fileName = basename($e->getFile(), ".php"); // Filename that trigger the exception $lineNumber = $e->getLine(); // Line number that triggers the exception die("[$fileName][$lineNumber] Database error: " . $e->getMessage() . '<br />'); } ?> |
Connected DROP TABLE: 0 rows CREATE TABLE: 0 rows INSERT INTO: 1 rows LAST_INSERT_ID (of the AUTO_INCREMENT column) is 1001 INSERT INTO: 2 rows LAST_INSERT_ID (of the AUTO_INCREMENT column) is 1002 Retrieve via column name: id=1001, name=peter Retrieve via column number: id=1001, name=peter Array ( [id] => 1001 [0] => 1001 [name] => peter [1] => peter ) Retrieve via column name: id=1002, name=paul Retrieve via column number: id=1002, name=paul Array ( [id] => 1002 [0] => 1002 [name] => paul [1] => paul ) Retrieve via column name: id=1003, name=patrick Retrieve via column number: id=1003, name=patrick Array ( [id] => 1003 [0] => 1003 [name] => patrick [1] => patrick ) Retrieve via column name: id=1001, name=peter Array ( [id] => 1001 [name] => peter ) Retrieve via column name: id=1002, name=paul Array ( [id] => 1002 [name] => paul ) Retrieve via column name: id=1003, name=patrick Array ( [id] => 1003 [name] => patrick ) Retrieve via column name: id=1001, name=peter stdClass Object ( [id] => 1001 [name] => peter ) Retrieve via column name: id=1002, name=paul stdClass Object ( [id] => 1002 [name] => paul ) Retrieve via column name: id=1003, name=patrick stdClass Object ( [id] => 1003 [name] => patrick )
Dissecting the Program
- To create a Database connection to MySQL Server:
$dbConn = new PDO("mysql:host=$host;port=$port;dbname=$dbname", $user, $pass);
where$host
is the MySQL server's hostname,$port
is the port number,$dbName
is the default database name,$user
and$pass
are the username/password for an authorized MySQL user. - The connection will be closed automatically at the end of the script. However, you could explicitly close the connection by setting it to
NULL
(which is often not necessary).$dbConn = NULL;
- Exception Handling: PDO support 3 error modes, which could be set via the
setAttribute()
function:PDO::ERRMODE_SILENT
(default): you need to check for errors yourself.PDO::ERRMODE_WARNING
: issue PHP warning and continue execution.PDO::ERRMODE_EXCEPTION
(recommended): throwPDOException
, to be handled viatry-catch
as shown in the above example. From thePDOException
object, you can invoke thegetFile()
,getLine()
andgetMessage()
to retrieve the full-path filename, line number, and error message where the exception is thrown.
- For CREATE/DROP TABLE, INSERT, DELETE, UPDATE, invoke
exec()
function, which returns the number of affected rows. - For SELECT, invoke
query()
function, which returns a resultset.query($sql)
returns a row-array resultset indexed by both column-name and column-number (starting from 0).query($sql, PDO::FETCH_ASSOC)
returns a row-array resultset indexed by column-name only (in an associative array).query($sql, PDO::FETCH_OBJ)
returns a row-object resultset with column-names as properties.
- You can use connection's
lastInsertId()
to retrieve the last insert id for theAUTO_INCREMENT
column.
Example 2: Prepared Statements
Prepared statements can reasonably guard against SQL injection attack (Wiki), and are recommended in production.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 |
<?php /** * PdoPreparedTest.php: Using PDO Prepared Statement. * For CREATE TABLE, DROP TABLE, INSERT, DELETE, UPDATE: * prepare() -> bindParam() -> execute() -> check rowCount(). * For SELECT: * prepare() -> bindParam() -> execute() -> check rowCount() -> fetch() a row or fetchAll() rows. */ // Define the MySQL database parameters. // Avoid global variables (which live longer than necessary) for sensitive data. $DB_HOST = 'localhost'; // MySQL server hostname $DB_PORT = '3306'; // MySQL server port number (default 3306) $DB_NAME = 'test'; // MySQL database name $DB_USER = 'testuser'; // MySQL username $DB_PASS = 'xxxx'; // password try { // Create a database connection to MySQL server. $dbConn = new PDO("mysql:host=$DB_HOST;port=$DB_PORT;dbname=$DB_NAME", $DB_USER, $DB_PASS); $dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Set error mode to exception echo 'Connected', '<br />'; $pstmt = $dbConn->prepare('DROP TABLE IF EXISTS `test`'); // Create prepared statement $pstmt->execute(); // Run prepared statement echo 'DROP TABLE: ', $pstmt->rowCount(), ' rows', '<br />'; $pstmt = $dbConn->prepare( 'CREATE TABLE IF NOT EXISTS `test` ( `id` INT AUTO_INCREMENT, `name` VARCHAR(20), PRIMARY KEY (`id`))'); // Create prepared statement $pstmt->execute(); // Run prepared statement echo 'CREATE TABLE: ', $pstmt->rowCount(), ' rows', '<br />'; // Create prepared statement with named parameters in the form of :paramName $pstmt = $dbConn->prepare( 'INSERT INTO `test` (`id`, `name`) VALUES (:id, :name)'); // Bind named parameters to program variables, with the type specified. $pstmt->bindParam(':id', $id, PDO::PARAM_INT); $pstmt->bindParam(':name', $name, PDO::PARAM_STR); $id = 2001; $name = 'john'; $pstmt->execute(); // Run prepared statement. echo 'INSERT INTO: ', $pstmt->rowCount(), ' rows', '<br />'; echo 'LAST_INSERT_ID (of the AUTO_INCREMENT column) is ', $dbConn->lastInsertId(), '<br />'; $id = 2002; $name = "jane"; $pstmt->execute(); // Run prepared statement again with different values. echo 'INSERT INTO: ', $pstmt->rowCount(), ' rows', '<br />'; echo 'LAST_INSERT_ID (of the AUTO_INCREMENT column) is ', $dbConn->lastInsertId(), '<br /><br />'; // Create prepared statement with positional (unnamed) parameters in the form of ? $pstmt = $dbConn->prepare('SELECT * FROM `test` WHERE `id` = ? AND `name` LIKE ?'); $pstmt->bindParam(1, $id, PDO::PARAM_INT); // Positional index starts at 1 $pstmt->bindParam(2, $name, PDO::PARAM_STR); $name = 'j%'; $id = 2001; $pstmt->execute(); // Run prepared statement. echo 'SELECT: ', $pstmt->rowCount(), ' rows', '<br />'; // Fetch resultset row-by-row // By default, resultset's row is indexed by BOTH column-name AND column-number (starting at 0). while ($row = $pstmt->fetch()) { echo 'Retrieve via column name: id=', $row['id'], ' name=', $row['name'], '<br />'; echo 'Retrieve via column number: id=', $row[0], ' name=', $row[1], '<br />'; print_r($row); // for showing the contents of resultset's row echo '<br />'; } echo '<br />'; // Run again with the same $name but different $id. $id = 2002; $pstmt->execute(); echo 'SELECT: ', $pstmt->rowCount(), ' rows', '<br />'; // Fetch entire resultset (all rows) into program buffer, with "FETCH_ASSOC" option. // Resultset's row is an associative array indexed by column-name only. $resultset = $pstmt->fetchAll(PDO::FETCH_ASSOC); // print_r($resultset); // all the rows in program buffer foreach ($resultset AS $row) { // Process each row in the buffer echo 'Retrieve via column name: id=', $row['id'], ' name=', $row['name'], '<br />'; print_r($row); // for showing the contents of resultset's row echo '<br />'; } echo '<br />'; // Run again with the same parameter values. $pstmt->execute(); echo 'SELECT: ', $pstmt->rowCount(), ' rows', '<br />'; // Fetch entire resultset (all rows) into program buffer, with "FETCH_OBJ" option. // Resultset's row is an object with column-names as properties. $resultset = $pstmt->fetchAll(PDO::FETCH_OBJ); foreach ($resultset AS $row) { // Process each row in the buffer echo 'Retrieve via column name: id=', $row->id, ' name=', $row->name, '<br />'; print_r($row); // for showing the contents of resultset's row echo '<br />'; } // Close the database connection (optional). $dbConn = NULL; } catch (PDOException $e) { $fileName = basename($e->getFile(), ".php"); // File that trigger the exception $lineNumber = $e->getLine(); // Line number that triggers the exception die("[$fileName][$lineNumber] Database error: " . $e->getMessage() . '<br />'); } ?> |
Connected DROP TABLE: 0 rows CREATE TABLE: 0 rows INSERT INTO: 1 rows LAST_INSERT_ID (of the AUTO_INCREMENT column) is 2001 INSERT INTO: 1 rows LAST_INSERT_ID (of the AUTO_INCREMENT column) is 2002 SELECT: 1 rows Retrieve via column name: id=2001 name=john Retrieve via column number: id=2001 name=john Array ( [id] => 2001 [0] => 2001 [name] => john [1] => john ) SELECT: 1 rows Retrieve via column name: id=2002 name=jane Array ( [id] => 2002 [name] => jane ) SELECT: 1 rows Retrieve via column name: id=2002 name=jane stdClass Object ( [id] => 2002 [name] => jane )
Dissecting the Program
- To use prepared statement, the operating sequence is
prepare()
,bindParam()
andexecute()
. - You can get the affected row thru statement's
rowCount()
. - For SELECT, invoke statement's
fetch()
orfetchAll()
to fetch a row or the entire resultset.fetchAll()
is more efficient, but requires more space. - PDO supports the following types of placeholders for parameters:
- Named Placeholders (recommended): The parameters are marked by
:paramName
, and bound by the name usingbindParam(placeHolderName, varName, paramType)
, as shown in the above example.
If a named parameter is used multiple times in the SQL statement, you need to bind only once for all the occurrences. For example,
$pstmt = $dbConn->prepare('SELECT * FROM `Users` WHERE `id` = :input OR `email` = :input'); $pstmt->bindParam(':input', $idOrEmail, PDO::PARAM_STR); // bind both occurrences $pstmt->execute();
Instead of binding the parameters viabindParam()
, you can also pack the actual values in an associative array and run theexecute($dataArray)
, for example,$pstmt = $dbConn->prepare('INSERT INTO `test` (`id`, `name`) VALUES (:id, :name)'); $dataArray = array('id' => 1001, 'name' => 'peter'); $pstmt->execute($dataArray);
- Positional (Unnamed) Placeholders: The parameters are marked by ?, and bind thru the parameter number (starting from 1), for example,
$pstmt = $dbConn->prepare('INSERT INTO `test` (`id`, `name`) VALUES (?, ?)'); $pstmt->bindParam(1, $id, PDO::PARAM_INT); $pstmt->bindParam(2, $name, PDO::PARAM_STR); $pstmt->execute();
You can also pack the actual values in an array in proper sequence and run theexecute($dataArray)
, for example,$pstmt = $dbConn->prepare('INSERT INTO `test` (`id`, `name`) VALUES (?, ?)'); $dataArray = array(1001, 'peter'); $pstmt->execute($dataArray);
- Named Placeholders (recommended): The parameters are marked by
- Fetch mode: You can also set the fetch mode via
setFetchMode()
, e.g.,$pstmt->setFetchMode(PDO::FETCH_ASSOC);
PDO::FETCH_ASSOC
: returns an array indexed by column name.PDO::FETCH_OBJ
: returns an anonymous object with property names corresponding to the column names.PDO::FETCH_BOTH
(default): returns an array indexed by both column name and column number (starting from 1).PDO::FETCH_CLASS
: assign the values of columns to the corresponding properties (variables) of the given class.
$pstmt->numRow()
: returns the number of rows affected.$dbConn->lastInsertID()
: returns the last insert ID (of theAUTO_INCREMENT
column) of the last INSERT operation.PDOException
: You can usegetFile()
,getLine()
,getMessage()
to get the filename, line number and exception error message, respectively.
Example 3: Test SQL Injection on Regular vs. Prepared Statements
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
<?php /** * PdoSQLInjectionTest.php: Checking SQL Injection for regular and prepared statements. */ // Define the MySQL database parameters. $DB_HOST = 'localhost'; // MySQL server hostname $DB_PORT = '3306'; // MySQL server port number (default 3306) $DB_NAME = 'test'; // MySQL database name $DB_USER = 'testuser'; // MySQL username $DB_PASS = 'xxxx'; // password try { // Create a database connection to MySQL server. $dbConn = new PDO("mysql:host=$DB_HOST;port=$DB_PORT;dbname=$DB_NAME", $DB_USER, $DB_PASS); $dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Set error mode to exception echo 'Connected', '<br />'; $pstmt = $dbConn->prepare('DROP TABLE IF EXISTS `test`'); // Create prepared statement $pstmt->execute(); // Run prepared statement echo 'DROP TABLE: ', $pstmt->rowCount(), ' rows', '<br />'; $pstmt = $dbConn->prepare( 'CREATE TABLE IF NOT EXISTS `test` ( `id` INT AUTO_INCREMENT, `name` VARCHAR(20), PRIMARY KEY (`id`))'); // Create prepared statement $pstmt->execute(); // Run prepared statement echo 'CREATE TABLE: ', $pstmt->rowCount(), ' rows', '<br />'; // Create prepared statement with named parameters in the form of :paramName $rowCount = $dbConn->exec("INSERT INTO `test` (`name`) VALUES ('peter'),('paul'),('patrick')"); echo 'INSERT INTO: ', $rowCount, ' rows', '<br /><br />'; // Use regular statement without sanitizing user input. $nameInput = "peter' OR '1'='1"; // SQL injection $resultset = $dbConn->query("SELECT * FROM `test` WHERE `name` = '$nameInput'"); // Check result foreach ($resultset as $row) { // Loop thru all rows in resultset print_r($row); // Show the contents of resultset's row echo '<br />'; } echo '<br />'; // Sanitizing user input via quote(), which escapes special characters. $sanitizedNameInput = $dbConn->quote($nameInput, PDO::PARAM_STR); print_r($sanitizedNameInput); echo '<br />'; $dbConn->query("SELECT * FROM `test` WHERE `name` = '$sanitizedNameInput'"); // SQL Syntax Error! } catch (PDOException $e) { $fileName = basename($e->getFile(), ".php"); // File that trigger the exception $lineNumber = $e->getLine(); // Line number that triggers the exception echo "[$fileName][$lineNumber] Database error: " . $e->getMessage() . '<br /><br />'; // continue } try { // Use prepared statement to guard against SQL injection. $pstmt = $dbConn->prepare('SELECT * FROM `test` WHERE `name` = :name'); $pstmt->bindParam(':name', $nameInput, PDO::PARAM_STR); // Bind with unsanitized user input $pstmt->execute(); // Run prepared statement. // Check result echo 'SELECT: ', $pstmt->rowCount(), ' rows', '<br />'; // 0 rows } catch (PDOException $e) { $fileName = basename($e->getFile(), ".php"); // File that trigger the exception $lineNumber = $e->getLine(); // Line number that triggers the exception die("[$fileName][$lineNumber] Database error: " . $e->getMessage() . '<br />'); } ?> |
Connected DROP TABLE: 0 rows CREATE TABLE: 0 rows INSERT INTO: 3 rows Array ( [id] => 1 [0] => 1 [name] => peter [1] => peter ) Array ( [id] => 2 [0] => 2 [name] => paul [1] => paul ) Array ( [id] => 3 [0] => 3 [name] => patrick [1] => patrick ) 'peter\' OR \'1\'=\'1' [PdoSQLInjectionTest][49] Database error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'peter\' OR \'1\'=\'1''' at line 1 SELECT: 0 rows
Dissecting the Program
- The first SELECT uses regular statement which is susceptible to SQL injection.
- We could use PDO's
quote()
function to escape special characters to prevent SQL injection, which resulted in SQL syntax error. However, thequote()
function is NOT recommended. - Instead, it is recommended to use prepared statement, which accepts but sanitizes and confines the input to a particular parameter.
Example 4: Transaction
Transaction ensures the so-called ACID (Atomic, Consistency, Integrity, Durability) properties. For example, to transfer money from one bank account to another account, it typically involves two SQL UPDATE statements. What if one of the statement fails? A transaction batches a group of SQL statements. The statements either ALL succeed or NONE succeed (atomic).
By default, MySQL statements are executing in an auto-commit mode, which commits every single statement. To enable transaction, use beginTransaction()
, which disables the auto-commit. You can then commit()
if all statements succeed; or rollBack()
if any of the statement fails.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
<?php /** * Testing PDO MySQL Transaction. * beginTransaction() -> query()/exec()/execute() -> commit() or rollBack(). */ // Define the MySQL database parameters. $DB_HOST = 'localhost'; // MySQL server hostname $DB_PORT = '3306'; // MySQL server port number (default 3306) $DB_NAME = 'test'; // MySQL database name $DB_USER = 'testuser'; // MySQL username $DB_PASS = 'xxxx'; // password try { // Create a database connection to MySQL server. $dbConn = new PDO("mysql:host=$DB_HOST;port=$DB_PORT;dbname=$DB_NAME", $DB_USER, $DB_PASS); $dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Set error mode to exception echo 'Connected', '<br />'; // Create an accounts table $pstmt = $dbConn->prepare('DROP TABLE IF EXISTS `accounts`'); $pstmt->execute(); echo 'DROP TABLE: ', $pstmt->rowCount(), ' rows', '<br />'; $pstmt = $dbConn->prepare( 'CREATE TABLE IF NOT EXISTS `accounts` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(20), `balance` INT)'); $pstmt->execute(); echo 'CREATE TABLE: ', $pstmt->rowCount(), ' rows', '<br />'; // Initialize the table $pstmt = $dbConn->prepare( 'INSERT INTO `accounts` (`name`, `balance`) VALUES (:name, :balance)'); $pstmt->bindParam(':name', $name, PDO::PARAM_STR); $pstmt->bindParam(':balance', $balance, PDO::PARAM_INT); $name = 'peter'; $balance = 1000; $pstmt->execute(); $name = 'paul'; $pstmt->execute(); // Same initial balance // Check the table $pstmt = $dbConn->prepare('SELECT * FROM `accounts`'); $pstmt->execute(); $resultset = $pstmt->fetchAll(PDO::FETCH_ASSOC); foreach ($resultset AS $row) { print_r($row); echo '<br />'; } echo '<br />'; } catch (PDOException $e) { $fileName = basename($e->getFile(), ".php"); // File that trigger the exception $lineNumber = $e->getLine(); // Line number that triggers the exception die("[$fileName][$lineNumber] Database error: " . $e->getMessage() . '<br />'); } // Use a transaction to transfer money from one account to another account. try { $dbConn->beginTransaction(); // Begin transaction by turning off the auto-commit mode $pstmt = $dbConn->prepare( 'UPDATE `accounts` SET `balance` = `balance` + :amount WHERE `name` = :name'); $pstmt->bindParam(':name', $name, PDO::PARAM_STR); $pstmt->bindParam(':amount', $amount, PDO::PARAM_INT); $name = 'peter'; $amount = -50; $pstmt->execute(); // Uncomment to force an exception // throw new PDOException('test'); $name = 'paul'; $amount = 50; $pstmt->execute(); // Commit the transaction if both statements succeed. $dbConn->commit(); // Back to auto-commit mode until another beginTransaction(). } catch (PDOException $e) { // Roll back all update if any of statement fails. echo 'Rolling back all updates...<br />'; $dbConn->rollBack(); } try { // Check the table after the transaction (commit or rollBack). $pstmt = $dbConn->prepare('SELECT * FROM `accounts`'); $pstmt->execute(); $resultset = $pstmt->fetchAll(PDO::FETCH_ASSOC); foreach ($resultset AS $row) { print_r($row); echo '<br />'; } } catch (PDOException $e) { $fileName = basename($e->getFile(), ".php"); // File that trigger the exception $lineNumber = $e->getLine(); // Line number that triggers the exception die("[$fileName][$lineNumber] Database error: " . $e->getMessage() . '<br />'); } ?> |
Connected DROP TABLE: 0 rows CREATE TABLE: 0 rows Array ( [id] => 1 [name] => peter [balance] => 1000 ) Array ( [id] => 2 [name] => paul [balance] => 1000 ) Array ( [id] => 1 [name] => peter [balance] => 950 ) Array ( [id] => 2 [name] => paul [balance] => 1050 )
Dissecting the Program
- The
beginTransaction()
method start a transaction, by disabling auto-commit. - The
commit()
method commits the transaction (if all statements succeed). - The
rollBack()
method, in thecatch
clause, rolls back the transaction (if any of the statements fails). No database update will be performed. commit()
androllBack()
also end the transaction, and return the connection to auto-commit, until anotherbeginTransaction()
is issued.- To try the
rollBack()
, either replace thecommit()
withrollBack()
, or issue a SQL statement that throws an exception to trigger therollBack()
in thecatch
clause. Observe that no record will be updated without acommit()
.
Example 5: Stored Procedures and Functions
Procedure/Functions are defined in the database, instead of PHP scripts. They are typically more efficient. The drawback is the codes are separated from your PHP codes, and could be hard to maintain.
[TODO]
mysqli By Examples (Deprecated)
Using mysqli's query()
The steps for using mysqli
to perform database operations are:
- Allocate a
mysqli
object, which establishes a connection to the database server, via$mysqli = new mysqli(hostname, username, password, databaseName, port);
- Use the
mysqli
'squery()
method to execute a SQL statement. ForSELECT
,query()
returns a result set; for other queries likeCREATE TABLE
andINSERT
,query()
returnsTRUE
orFALSE
. For example,// query() returns TRUE/FALSE for INSERT INTO $mysqli->query('INSERT INTO ......'); // query() returns a result set for SELECT $resultSet = $mysqli->query('SELECT * FROM ......');
- For
SELECT
query, process the result set. The result set is kept in an object ofmysqli_result
, which contains properties and methods for operating the result set. A result set is associated with a row cursor, which initially pointing at the first row. The methodfetch_assoc()
fetches the current row from the result set, advanced the result set's cursor to the next row, and returns an associative array indexed by column names. The propertynum_rows
stores the number of rows in the result set.for ($r = 0; $r < $resultSet->num_rows; ++$r) { // fetch a row as associative array $row = $resultSet->fetch_assoc(); // Get each cell of the row as $row['columnName'] ...... } $resultSet->close(); // close the result set
- Close the database connection:
$mysqli->close();
Example: mysqli->query()
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
<?php // mysqli_query.php // Define database related constants define('DB_HOSTNAME', 'localhost'); define('DB_USERNAME', 'myuser'); define('DB_PASSWORD', 'xxxx'); define('DB_DATABASE', 'test'); define('DB_PORT', 3306); // Connect to the MySQL server and set the default database $mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT); // Assert no error !$mysqli->connect_errno or die("Error: Failed to CONNECT: ({$mysqli->connect_errno}) {$mysqli->connect_error}"); echo 'INFO: Connected to MySQL at ' . DB_HOSTNAME . ':' . DB_PORT . '/' . DB_DATABASE . ' (' . DB_USERNAME . ')<br />'; // Invoke query() to execute a DROP TABLE statement, which returns TRUE/FALSE $mysqli->query('DROP TABLE IF EXISTS Coffees') or die("Error: DROP TABLE failed: ({$mysqli->errno}) {$mysqli->error}"); echo 'INFO: Table Coffees dropped<br />'; // CREATE TABLE string $sqlStr = <<<_END CREATE TABLE Coffees ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(32) NOT NULL, price DECIMAL(5,2) NOT NULL DEFAULT 999.99, PRIMARY KEY (id), INDEX (name) ) _END; // Invoke query() to execute a CREATE TABLE statement, which returns TRUE/FALSE $mysqli->query($sqlStr) or die("Error: CREATE TABLE failed: ({$mysqli->errno}) {$mysqli->error}"); echo 'INFO: Table Coffees created<br />'; // INSERT string $sqlStr = <<<_END INSERT INTO Coffees (name, price) VALUES ('Espresso', 3.19), ('Cappuccino', 3.29), ('Caffe Latte', 3.39), ('Caffe Mocha', 3.49), ('Brewed Coffee', 3.59) _END; // Invoke query() to execute an INSERT INTO statement, which returns TRUE/FALSE $mysqli->query($sqlStr) or die("Error: INSERT failed: ({$mysqli->errno}) {$mysqli->error}"); // Get the number of rows affected from property affected_rows echo "INFO: {$mysqli->affected_rows} row(s) inserted"; // Invoke query() to execute a SELECT query, which returns a buffered resultset $resultSet = $mysqli->query('SELECT * FROM Coffees') or die("Error: SELECT failed: ({$mysqli->errno}) {$mysqli->error}"); // Process the resultset and display in an HTML table echo <<<_END <table> <tr> <th>ID</th> <th>Name</th> <th>Price</th> </tr> _END; // Fetch each row and print table detail row // fetch_assoc() returns FALSE if there is no more row while ($row = $resultSet->fetch_assoc()) { echo <<<_END <tr> <td>{$row['id']}</td> <td>{$row['name']}</td> <td>{$row['price']}</td> </tr> _END; } echo <<<_END </table> _END; $resultSet->close(); // Close the resultset // Disconnect From MySQL server $mysqli->close(); echo 'INFO: Connection Closed<br />'; ?> |
Notes
- The
fetch_assoc()
returns an associative array, containing the current row in the resultset. You can then retrieve the values of the columns via the column names.fetch_assoc()
returns FALSE when there is no more row in the resultset. - By default (with
resultMode
ofMYSQLI_STORE_RESULT
), runningquery()
withSELECT
statement returns a buffered resultset, with the number of rows of the resultset stored in propertynum_rows
. You can also use thenum_rows
to control the loop:// The query() returns a buffered resultset, with number of rows in num_rows. for ($r = 0; $r < $resultSet->num_rows; ++$r) { // Fetch current row into an associative array called $row $row = $resultSet->fetch_assoc(); echo <<<_END <tr> <td>{$row['id']}</td> <td>{$row['name']}</td> <td>{$row['price']}</td> </tr> _END; }
mysqli's query(), real_query() and multi_query()
mysqli
provides 3 query methods: query()
, real_query()
, multi_query()
.
query()
is the most commonly used. ForSELECT
,query()
combines executing SQL statement with fetching of the result set. For other SQL statement,query()
returnsTRUE
orFALSE
.real_query()
executes the SQL statement and returnsTRUE
orFALSE
. ForSELECT
, it does not return the result set. You need to issue astore_result()
to fetch and buffer the result set (oruse_result()
to initiate fetching of result set but without buffering).multi_query()
is used for executing multiple SQL statements (separated by semi-colons) without fetching the result sets. You can usestore_result()
to fetch the first result set;more_results()
to check if there is more result set, andnext_result()
to prepare the next result set.multi_query()
is needed for executing SQL procedure which returns more than one result set.
Example: real_query() and multi_query()
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
<?php // mysqli_real_and_multi_query.php // Define database related constants define('DB_HOSTNAME', 'localhost'); define('DB_USERNAME', 'myuser'); define('DB_PASSWORD', 'xxxx'); define('DB_DATABASE', 'test'); define('DB_PORT', 3306); // Connect to the MySQL server and set the default database $mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT); !$mysqli->connect_errno or die("Error: Failed to CONNECT to MySQL: ({$mysqli->connect_errno}) {$mysqli->connect_error}"); echo 'INFO: Connected to MySQL at ' . DB_HOSTNAME . ':' . DB_PORT . '/' . DB_DATABASE . ' (' . DB_USERNAME . ')<br />'; // Use query() to execute a DROP TABLE statement, which returns TRUE/FALSE $mysqli->query('DROP TABLE IF EXISTS Coffees') or die("Error: DROP TABLE Coffees failed: ({$mysqli->errno}) {$mysqli->error}"); echo 'INFO: Table Coffees dropped<br />'; // CREATE TABLE $sqlStr = <<<_END CREATE TABLE Coffees ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(32) NOT NULL, price DECIMAL(5,2) NOT NULL DEFAULT 999.99, PRIMARY KEY (id), INDEX (name) ) _END; // Use query() to execute a CREATE TABLE statement, which returns TRUE/FALSE $mysqli->query($sqlStr) or die("Error: CREATE TABLE Coffees failed: ({$mysqli->errno}) {$mysqli->error}"); echo 'INFO: Table Coffees created<br />'; // INSERT $sqlStr = <<<_END INSERT INTO Coffees (name, price) VALUES ('Espresso', 3.19), ('Cappuccino', 3.29), ('Caffe Latte', 3.39), ('Caffe Mocha', 3.49), ('Brewed Coffee', 3.59) _END; // Use query() to execute an INSERT INTO statement, which returns TRUE/FALSE $mysqli->query($sqlStr) or die("Error: INSERT INTO Coffees failed: ({$mysqli->errno}) {$mysqli->error}"); // Get the number of rows affected from affected_rows echo "INFO: {$mysqli->affected_rows} row(s) inserted"; /* * Use real_query() to execute a single SELECT query, which returns TRUE/FALSE */ $mysqli->real_query('SELECT * FROM Coffees') or die("Error: SELECT failed: ({$mysqli->errno}) {$mysqli->error}"); // real_query(), unlike query(), does not return the result set. // Need to use store_result() (or use_result()) to fetch the resultset $resultSet = $mysqli->store_result() or die("Error: Store resultset failed: ({$mysqli->errno}) {$mysqli->error}"); // Call helper function to tabulated the resultset tabulate_resultset($resultSet); $resultSet->close(); // Close the result set /* * Use multi_query() to execute multiple SELECT statements, which returns TRUE/FALSE */ $mysqli->multi_query('SELECT * FROM Coffees; SELECT * FROM Coffees WHERE price >= 3.49') or die("Error: SELECT failed: ({$mysqli->errno}) {$mysqli->error}"); do { // Store and process the resultset $resultSet = $mysqli->store_result() or die("Error: Store resultset failed: ({$mysqli->errno}) {$mysqli->error}"); tabulate_resultset($resultSet); $resultSet->close(); } while ($mysqli->more_results() and $mysqli->next_result()); // more_results() checks if there is more resultset // next_result() prepares the next resultset for fetching // Disconnect From MySQL server $mysqli->close(); echo 'INFO: Connection Closed<br />'; /* * Helper function to tabulate resultset */ function tabulate_resultset($resultSet) { echo '<table><tr>'; // Get fields' name and print table header row $fields = $resultSet->fetch_fields(); // Get all fields foreach ($fields as $field) { echo "<th>{$field->name}</th>"; } echo '</tr>'; // Fetch each row and print table detail row while ($row = $resultSet->fetch_assoc()) { echo '<tr>'; foreach ($row as $item) { echo "<td>$item</td>"; } echo '</tr>'; } echo '</table>'; } ?> |
Notes
- Two methods are available for fetching the resultset after
real_query()
ormulti_query()
:store_result()
anduse_result()
.store_result()
fetches the entire resultset from the server and returns to the client, i.e., buffered. You can use properties such asnum_rows
to get the number of rows of the result set, ordata_seek()
to position the cursor.use_result()
fetches the rows one by one, i.e., unbuffered. You cannot usenum_rows
ordata_seek()
.
store_result()
to retrieve a buffered resultset, unless you have a good reason not to do so. resultset->close()
is the same asresultset->free()
andresultset->free_result().
- If you get this error: "Commands out of sync; you can't run this command now", you need to
close()
the previous resultset, before executing another query.
Prepared Statements
A prepared statement (or parameterized statement) contains placeholders for input parameters. You can reuse the same prepared statement many times, with different inputs. Prepared statements are often pre-compiled, which are more efficient than the normal statements. They are also not susceptible to SQL injection attack. Prepared statements are, therefore, RECOMMENDED for production, over the normal statements.
The steps in using prepared statements are:
- Allocate a prepared statement, with placeholders indicated as
?
, viaprepare()
. - Set the inputs for the placeholders, via
bind_param()
. - Execute the prepared statement with the inputs, via
execute()
. - For
SELECT
query, retrieve the buffered result set viastore_result()
; followed bybind_result()
to bind the columns with variables; andfetch()
to fetch a row with columns bound to the variables. - Repeat Step 2 and 4 for another execution.
- Deallocate the prepared statement, via
close()
.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
<?php // mysqli_prepared_statement.php // Define database related constants define('DB_HOSTNAME', 'localhost'); define('DB_USERNAME', 'myuser'); define('DB_PASSWORD', 'xxxx'); define('DB_DATABASE', 'test'); define('DB_PORT', 3306); // Connect to the MySQL server and set the default database $mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT); !$mysqli->connect_errno or die("Error: Failed to CONNECT: ({$mysqli->connect_errno}) {$mysqli->connect_error}"); echo 'INFO: Connected to MySQL at ' . DB_HOSTNAME . ':' . DB_PORT . '/' . DB_DATABASE . ' (' . DB_USERNAME . ')<br />'; // Allocate a prepared statement with placeholders indicated by ? $pStmt = $mysqli->prepare("SELECT id, name, price FROM Coffees WHERE price BETWEEN ? AND ?") or die("Error: create prepared failed: ({$mysqli->errno}) {$mysqli->error}"); // Bind inputs to the placeholders $priceLow = 3.29; $priceHigh = 3.48; $pStmt->bind_param('dd', $priceLow, $priceHigh) // 'dd': two doubles and $pStmt->execute() and $pStmt->store_result() and $pStmt->bind_result($id, $name, $price) // bind columns to these variables or die("Error: Run prepared failed: ({$pStmt->errno}) {$pStmt->error}"); echo <<<_END <table> <tr> <th>ID</th> <th>Name</th> <th>Price</th> </tr> _END; while ($pStmt->fetch()) { echo <<<_END <tr> <td>$id</td> <td>$name</td> <td>$price</td> </tr> _END; } echo '</table>'; // Deallocate prepared statement $pStmt->close(); // An INSERT INTO prepared statement $pStmt = $mysqli->prepare("INSERT INTO Coffees (name, price) VALUES (?, ?)") or die("Error: create prepared failed: ({$mysqli->errno}) {$mysqli->error}"); $name = 'Caffe Americano'; $price = 3.69; $pStmt->bind_param('sd', $name, $price) // 'sd': string, double and $pStmt->execute() or die("Error: run prepared failed: ({$pStmt->errno}) {$pStmt->error}"); echo "INFO: {$pStmt->affected_rows} row(s) inserted<br />"; // Run the INSERT INTO prepared statement again with different inputs $name = 'Vanilla Latte'; $pStmt->bind_param('sd', $name, $price) // 'sd': string, double and $pStmt->execute() or die("Error: run prepared failed: ({$pStmt->errno}) {$pStmt->error}"); echo "INFO: {$pStmt->affected_rows} row(s) inserted<br />"; $pStmt->close(); // Disconnect From MySQL server $mysqli->close(); echo 'INFO: Connection Closed<br />'; ?> |
Explanations:
prepare(sqlstr)
: Allocate a prepared statement with the given SQL string.bind_parm(types, var1, var2, ...)
: The types indicate the type of variables, withi
for integer,d
for double,s
for string andb
for blog. For example, "sssd
" means 3 strings followed by a double.execute()
runs the prepared statement, and setaffected_rows
for INSERT, DELETE, etc.store_result()
fetches the buffered resultset, and set propertynum_rows
.bind_result()
binds the columns of the resultset with the given variables.fetch()
fetches the current row from the resultset. You can retrieve the columns via the variables bound inbind_result()
.fetch()
returns FALSE when there is no more row in resultset.- The order for using prepared statement for query with resultset (such as SELECT) is
prepare()
⇒bind_param()
⇒execute()
⇒store_result()
⇒bind_result()
⇒fetch()
. Thestore_result()
retrieves a buffered resultset and sets thenum_rows
. - The order for using prepared statement for query without resultset (such as INSERT, DELETE and UPDATE) is
prepare()
⇒bind_param()
⇒execute()
. Theexecute()
sets theaffected_row
. - All these functions return FALSE on error. Hence, they can be chained via "and"; with error reporting via "or".
- Take note that
prepare()
reports error via$mysqli
($mysqli->error
and$mysqli->errno
); whileexecute()
reports the error via the prepared statement ($pStmt->error
and$pStmt->errno
). However,bind_param()
andbind_result()
do not report error on prepared statement (RDBMS), but via the PHP. - In
bind_param()
, you can bind a PHP's NULL to SQL's NULL with 's' flag. - Don't use the
get_result()
function, which is not supported in many PHP platforms.
Stored Procedures and Functions
Example: Stored Procedure without Resultset
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
<?php // mysqli_procedure.php // Define database related constants define('DB_HOSTNAME', 'localhost'); define('DB_USERNAME', 'myuser'); define('DB_PASSWORD', 'xxxx'); define('DB_DATABASE', 'test'); define('DB_PORT', 3306); // Connect to the MySQL server and set the default database $mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT); !$mysqli->connect_errno or die("Error: Failed to CONNECT: ({$mysqli->connect_errno}) {$mysqli->connect_error}"); echo 'INFO: Connected to MySQL at ' . DB_HOSTNAME . ':' . DB_PORT . '/' . DB_DATABASE . ' (' . DB_USERNAME . ')<br />'; // A PROCEDURE with IN parameters, no result set $mysqli->query("DROP PROCEDURE IF EXISTS addItem") or die("Error: DROP PROCEDURE failed: ({$mysqli->errno}) {$mysqli->error}"); $sqlStr = <<<_END CREATE PROCEDURE addItem (IN inName VARCHAR(32), IN inPrice DECIMAL(5,2)) BEGIN INSERT INTO Coffees (name, price) VALUES (inName, inPrice); END _END; $mysqli->query($sqlStr) or die("Error: CREATE PROCEDURE failed: ({$mysqli->errno}) {$mysqli->error}"); // CAll the procedure $mysqli->query("CALL addItem('Green tea', 2.89)") or die("Error: CALL failed: ({$mysqli->errno}) {$mysqli->error}"); echo "INFO: $mysqli->affected_rows row(s) inserted.<br />"; // CAll the procedure again $mysqli->query("CALL addItem('Yellow tea', 2.99)") or die("Error: CALL failed: ({$mysqli->errno}) {$mysqli->error}"); echo "INFO: $mysqli->affected_rows row(s) inserted.<br />"; // Disconnect From MySQL server $mysqli->close(); echo 'INFO: Connection Closed<br />'; ?> |
Explanation [TODO]
Notes
- We cannot use the prepared statement to run SQL procedure. (Error: This command is not supported in the prepared statement protocol yet.)
Example: Stored Procedure with Result Set
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
<?php // mysqli_procedure_resultset.php // Define database related constants define('DB_HOSTNAME', 'localhost'); define('DB_USERNAME', 'myuser'); define('DB_PASSWORD', 'xxxx'); define('DB_DATABASE', 'test'); define('DB_PORT', 3306); // Connect to the MySQL server and set the default database $mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT); !$mysqli->connect_errno or die("Error: Failed to CONNECT: ({$mysqli->connect_errno}) {$mysqli->connect_error}"); echo 'INFO: Connected to MySQL at ' . DB_HOSTNAME . ':' . DB_PORT . '/' . DB_DATABASE . ' (' . DB_USERNAME . ')<br />'; // A PROCEDURE with resultset $sqlStr = <<<_END CREATE PROCEDURE listItemInPriceRange (IN low DECIMAL(5,2), IN high DECIMAL(5,2)) LANGUAGE SQL DETERMINISTIC READS SQL DATA SQL SECURITY INVOKER BEGIN SELECT id, name, price FROM Coffees WHERE price BETWEEN low AND high; END _END; $mysqli->query("DROP PROCEDURE IF EXISTS listItemInPriceRange") and $mysqli->query($sqlStr) or die("Error: DROP/CREATE PROCEDURE failed: ({$mysqli->errno}) {$mysqli->error}"); // CALL procedure with result set $resultSet = $mysqli->query("CALL listItemInPriceRange(1.99, 3.29)") or die("Error: CALL failed: ({$mysqli->errno}) {$mysqli->error}"); tabulate_resultset($resultSet); $resultSet->close(); // The CALL returns two resultsets?! Discard 2nd resultset before issuing another CALL. // Else: "Commands out of sync; you can't run this command now". $mysqli->next_result(); // Another call to procedure using real_query() $mysqli->real_query("CALL listItemInPriceRange(3.29, 3.49)") or die("Error: CALL failed: ({$mysqli->errno}) {$mysqli->error}"); $resultSet = $mysqli->store_result() or die("Error: Store failed: ({$mysqli->errno}) {$mysqli->error}"); tabulate_resultset($resultSet); $resultSet->close(); // Disconnect From MySQL server $mysqli->close(); echo 'INFO: Connection Closed<br />'; /* * Helper function to tabulate resultset */ function tabulate_resultset($resultSet) { echo '<table><tr>'; // Get fields' name and print table header row $fields = $resultSet->fetch_fields(); foreach ($fields as $field) { echo "<th>{$field->name}</th>"; } echo '</tr>'; // Fetch each row and print table detail row while ($row = $resultSet->fetch_assoc()) { echo '<tr>'; foreach ($row as $rowItem) { echo "<td>$rowItem</td>"; } echo '</tr>'; } echo '</table>'; } ?> |
Explanation [TODO]
Example: Procedure with OUT parameter
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
<?php // mysqli_procedure_outparam.php // Define database related constants define('DB_HOSTNAME', 'localhost'); define('DB_USERNAME', 'myuser'); define('DB_PASSWORD', 'xxxx'); define('DB_DATABASE', 'test'); define('DB_PORT', 3306); // Connect to the MySQL server and set the default database $mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT); !$mysqli->connect_errno or die("Error: Failed to CONNECT: ({$mysqli->connect_errno}) {$mysqli->connect_error}"); echo 'INFO: Connected to MySQL at ' . DB_HOSTNAME . ':' . DB_PORT . '/' . DB_DATABASE . ' (' . DB_USERNAME . ')<br />'; // A PROCEDURE with out parameter $sqlStr = <<<_END CREATE PROCEDURE getMinPrice(OUT outMinPrice DECIMAL(5,2)) LANGUAGE SQL DETERMINISTIC READS SQL DATA SQL SECURITY INVOKER BEGIN SELECT min(price) INTO outMinPrice FROM Coffees; END _END; $mysqli->query("DROP PROCEDURE IF EXISTS getMinPrice") and $mysqli->query($sqlStr) or die("Error: DROP/CREATE PROCEDURE failed: ({$mysqli->errno}) {$mysqli->error}"); $mysqli->query("SET @minPrice = 0") and $mysqli->query("CALL getMinPrice(@minPrice)") or die("Error: CALL failed: ({$mysqli->errno}) {$mysqli->error}"); $resultSet = $mysqli->query("SELECT @minPrice AS result") or die("Error: SELECT failed: ({$mysqli->errno}) {$mysqli->error}"); $row = $resultSet->fetch_assoc(); $minPrice = $row['result']; echo "The min price is $minPrice.<br />"; $resultSet->close(); // Disconnect From MySQL server $mysqli->close(); echo 'INFO: Connection Closed<br />'; ?> |
Explanation [TODO]
Example: Function
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
<?php // mysqli_function.php // Define database related constants define('DB_HOSTNAME', 'localhost'); define('DB_USERNAME', 'myuser'); define('DB_PASSWORD', 'xxxx'); define('DB_DATABASE', 'test'); define('DB_PORT', 3306); // Connect to the MySQL server and set the default database $mysqli = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT); !$mysqli->connect_errno or die("Error: Failed to CONNECT: ({$mysqli->connect_errno}) {$mysqli->connect_error}"); echo 'INFO: Connected to MySQL at ' . DB_HOSTNAME . ':' . DB_PORT . '/' . DB_DATABASE . ' (' . DB_USERNAME . ')<br />'; // A function with a return value $sqlStr = <<<_END CREATE FUNCTION getMaxPrice() RETURNS DECIMAL(5,2) LANGUAGE SQL DETERMINISTIC READS SQL DATA SQL SECURITY INVOKER BEGIN DECLARE maxPrice DECIMAL(5,2); SELECT max(price) INTO maxPrice FROM Coffees; RETURN maxPrice; END _END; $mysqli->query("DROP FUNCTION IF EXISTS getMaxPrice") and $mysqli->query($sqlStr) or die("Error: DROP/CREATE FUNCTION failed: ({$mysqli->errno}) {$mysqli->error}"); // Invoke function $resultSet = $mysqli->query("SELECT getMaxPrice() AS result") or die("Error: SELECT failed: ({$mysqli->errno}) {$mysqli->error}"); $row = $resultSet->fetch_assoc(); $maxPrice = $row['result']; echo "The max price is $maxPrice.<br />"; $resultSet->close(); // Disconnect From MySQL server $mysqli->close(); echo 'INFO: Connection Closed<br />'; ?> |
Explanation [TODO]
Transaction
[TODO]
Processing HTML Forms
Handing Form Data
PHP Pre-defined Superglobal Variables
PHP pre-defined a few Superglobal variables - Superglobals are built-in variables that are always available in all scopes. They are associative array.
- $_GET: an associative array of GET request key-value pairs.
- $_POST: an associative array of POST request key-value pairs.
- $_COOKIE: an associative array of key-value pairs in the request cookie.
- $_REQUEST: contains GET, POST and COOKIE data from the request message
- $_FILES: uploaded files
- $_SESSION: for session management
- Others: $_SERVER, $GLOBALS, $_ENV,
You can retrieve form data from associate array $_GET
(for GET method parameters) $_POST
(for POST method parameters), or $_REQUEST
(both GET and POST parameters and COOKIE data). You can use the isset()
function to check if a given element exists in an associative array.
Always sanitize inputs from the client before using them or echoing back to prevent malicious attacks such as SQL injection and HTML injection. You can the PHP function htmlentities()
(or htmlspecialchars()
), which convert special characters to HTML entities (e.g., >
to >
, <
to <
, "
to "
, &
to &
).
Example
[TODO]
Uploading Files
To upload a file to the server
- On the client-side, provide an HTML
<form>
with attributesenctype="multipart/form-data"
andmethod="POST"
(attributeaction
default to the current page); and an<input>
element withtype="file"
which displays a text field and a browse file button andname="upFilename"
. - Upon submitting the form, the file is uploaded to a temporary directory in the server. Information about the uploaded file is kept in the superglobal associative array
$_FILES['upFilename']
. You can invokemove_uploaded_file(tmpFile, newFile)
to move the file from thetmp
directory to the desired location.
Example
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
<!DOCTYPE html> <html> <head><title>PHP File Upload</title></head> <body> <h2>File Upload</h2> <form method='post' enctype='multipart/form-data'> Select File: <input type='file' name='upFile' /><br /> <input type='submit' value='Upload' /> <input type='hidden' name='max_file_size' value='4096' /> </form> <?php // file_upload.php echo '<br /><p>The upload_max_filesize in php.ini is ' . ini_get('upload_max_filesize') . '</p>'; echo '<p>The post_max_size in php.ini is ' . ini_get('post_max_size') . '</p>'; if ($_FILES) { if ($_FILES['upFile']['error'] !== 0) { // Handle upload error $errMsg = ''; switch ($_FILES['upFile']['error']) { case 1: $errMsg = 'File exceeded upload_max_filesize in php.ini'; break; case 2: $errMsg = 'File exceeded max_file_size in form hidden field'; break; // max_file_size is not supported in any browser. // You should rely on upload_max_filesize (and post_max_size) // in php.ini to limit file size case 3: $errMsg = 'File only partially uploaded'; break; case 4: $errMsg = 'No file uploaded'; break; case 6: $errMsg = 'Missing temp directory'; break; case 7: $errMsg = 'Failed to write to disk'; break; case 8: $errMsg = 'A PHP extension stopped the file upload'; break; default: $errMsg = 'Other errors?!'; } echo "$errMsg<br />"; } else { // Print information about the uploaded file echo <<<_END <p><strong>File Uploaded</strong><br /> The file name is {$_FILES['upFile']['name']}<br /> The file mime-type is {$_FILES['upFile']['type']}<br /> The file size is {$_FILES['upFile']['size']}<br /> The file tmp-name is {$_FILES['upFile']['tmp_name']}</p> _END; // Move the uploaded file from tmp to desired location $upFilename = $_FILES['upFile']['name']; $tmpFilename = $_FILES['upFile']['tmp_name']; // Need to create an "images" sub-directory move_uploaded_file($tmpFilename, "images/$upFilename"); echo "<img src='images/$upFilename' />"; } } ?> </body> </html> |
The $_FILES
associative array maintains these information about the uploaded file:
- $_FILES['upFilename']['name']: the filename and file type of the uploaded file, e.g.,
myimage.jpg
. - $_FILES['upFilename']['type']: the MIME type of the uploaded file, e.g.,
image/jpeg
,text/plain
,text/html
,text/xml
. - $_FILES['upFilename']['size']: the size of the uploaded file.
- $_FILES['upFilename']['tmp_name']: the temporary filename stored in the server.
- $_FILES['upFilename']['error']: the error code. See the codes for the possible error codes.
For security:
- You may check the MIME type of the uploaded file and process only certain file types.
- Check the size of the uploaded file.
- Do not use the uploaded filename. Instead, hardcode your filename.
- If you wish to use the uploaded filename, strip away all the non-alphanumeric and dot characters and possibly convert it to lower case, e.g.,
$upFilename = strtolower(preg_replace("/[^A-Za-z0-9.]/", "", $upFilename));
Uploading Multiple Files with multiple <input type="file"> Elements
We can upload multiple files in one <form>
, by providing multiple <input type="file" name="xxx">
elements with different name
s.
Uploading Multiple Files with one <input type="file"> Element
The <input> element shall be as follows, where []
indicating multiple items.
<input type="file" name="upFile[]" multiple />
In PHP, the $_FILES['upFile']['tmp_name']
becomes an array, e.g.,
<?php print_r($_FILES['upFile']['tmp_name']); ?>
You can access each of the file via $_FILES['upFile']['tmp_name'][0]
, $_FILES['upFile']['tmp_name'][1]
, etc. You can use count($_FILES['upFile']['tmp_name'])
to get the number of files uploaded.
For example,
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
<!DOCTYPE html> <html> <head><title>PHP File Upload</title></head> <body> <h2>Multiple File Upload</h2> <p>Hold down the Ctrl-key or Shift-key to select multiple file.</p> <form method='post' enctype='multipart/form-data'> Select File: <input type='file' name='upFile[]' multiple /><br /> <input type='submit' value='Upload' /> <input type='hidden' name='max_file_size' value='4096' /> </form> <?php // file_upload.php echo '<br /><p>The upload_max_filesize in php.ini is ' . ini_get('upload_max_filesize') . '</p>'; echo '<p>The post_max_size in php.ini is ' . ini_get('post_max_size') . '</p>'; if ($_FILES) { $numFiles = count($_FILES['upFile']['tmp_name']); /* * Process each file uploaded */ for ($i = 0; $i < $numFiles; ++$i) { if ($_FILES['upFile']['error'][$i] !== 0) { // Handle upload error $errMsg = ''; switch ($_FILES['upFile']['error'][$i]) { case 1: $errMsg = 'File exceeded upload_max_filesize in php.ini'; break; case 2: $errMsg = 'File exceeded max_file_size in form hidden field'; break; // max_file_size is not supported in any browser. // You should rely on upload_max_filesize (and post_max_size) // in php.ini to limit file size case 3: $errMsg = 'File only partially uploaded'; break; case 4: $errMsg = 'No file uploaded'; break; case 6: $errMsg = 'Missing temp directory'; break; case 7: $errMsg = 'Failed to write to disk'; break; case 8: $errMsg = 'A PHP extension stopped the file upload'; break; default: $errMsg = 'Other errors?!'; } echo "$errMsg<br />"; } else { // Print information about the uploaded file echo <<<_END <p><strong>File Uploaded</strong><br /> The file name is {$_FILES['upFile']['name'][$i]}<br /> The file mime-type is {$_FILES['upFile']['type'][$i]}<br /> The file size is {$_FILES['upFile']['size'][$i]}<br /> The file tmp-name is {$_FILES['upFile']['tmp_name'][$i]}</p> _END; // Move the uploaded file from tmp to desired location $upFilename = $_FILES['upFile']['name'][$i]; $tmpFilename = $_FILES['upFile']['tmp_name'][$i]; // Need to create an "images" sub-directory move_uploaded_file($tmpFilename, "images/$upFilename"); echo "<img src='images/$upFilename' />"; } } } ?> </body> </html> |
Managing Session
Session
HTTP is a stateless protocol. That is, the current page has no knowledge of what have been done in the previous pages.
PHP supports session management, via the superglobal associative array called $_SESSION
. When a session is created, a web user is assigned a unique session_id
. This session_id
is propagated in all subsequent requests for that session via either cookie or URL-rewriting. PHP processor associates this session_id
with a server-side $_SESSION
associative array, which can be used to store session information such as username, role, and shopping cart.
To use session:
- Start a session via
session_start()
. Thesession_start()
starts a new session or resume the existing session. - The superglobal associative array
$_SESSION
maintain data of the session.- You can check if a key-value pair exists via
isset($_SESSION['key'])
. - You can set a key-value pair for this session via
$_SESSION['key'] = value
. - You can retrieve value of a particular key via
$_SESSION['key']
. - You can remove a key-value pair via
unset($_SESSION['key'])
. - You can use function
session_id()
to retrieve the session ID.
- You can check if a key-value pair exists via
- To terminate the session explicitly, use
session_write_close()
.
Example
This script starts a new session and maintains the access count in $_SESSION['count']
.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<?php // session_start.php // start a session or resume the existing session session_start(); // Increment counter if (!isset($_SESSION['count'])) { $_SESSION['count'] = 0; } else { $_SESSION['count']++; } echo <<<_END <p>You have accessed this page {$_SESSION['count']} times</p> <p><a href="session_start.php">Refresh</a></p> <p><a href="session_end.php">End Session</a></p> _END; ?> |
This script unsets the access count and ends the session.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<?php // session_end.php // start a session or resume the existing session session_start(); // Empty the session data $_SESSION=array(); // Expire Cookie if (session_id() != "" || isset($_COOKIE[session_name()])) setcookie(session_name(), '', time()-2592000, '/'); // Terminate session session_destroy(); echo <<<_END <p>Session Ended</p> <p><a href="session_start.php">Start Session</a></p> _END; ?> |
PHP Session Timeout
The maximum duration (in seconds) for PHP session timeout can be configured in "php.ini
" (e.g., In Ubuntu, "/etc/php5/apache2/php.ini
"):
session.gc_maxlifetime = 1440
# default of 1440 seconds = 24 minutes
You can retrieve the configuration parameters during runtime via ini_get()
function, e.g.,
echo ini_get('session.gc_maxlifetime');
You can use function ini_set('session.cookie_lifetime', 1200)
to set the lifetime of as session cookie to 1200 seconds (20 minutes).
Login/Logout
The procedure is:
- Authenticate the user's credential.
- Create a new session via
session_start()
, and place some tokens inside the session, e.g.$_SESSION['isLogin'] = TRUE.
- On all the other pages, resume the session ALSO via
session_start()
, and check if$_SESSION['isLogin']
is there, which indicates a resumed session rather than a new session.
For example,
[TODO]
Logging
Proper logging of information and errors is important in a production environment, for security, debugging and auditing. There are many logging plug-ins for PHP available.
kLogger
A simple and lightweight logging plug-in, available at http://codefury.net/projects/klogger/ or https://github.com/katzgrau/KLogger. It can log various priority messages (e.g., DEBUG
, INFO
, ERROR
) with time-stamp. It, however, logs only to file, which is named log_YYYY_MM_DD.txt
under a directory which is configurable.
To use kLogger:
<?php // kLogger_example.php require_once('/path/to/kLogger.php'); $logDir = "logs"; // The log directory // Set the default timezone for the timestamp in log messages date_default_timezone_set('Asia/Singapore'); // Get a singleton instance of kLogger, by providing // the log directory name. By default, the log file is named // log_YYYY-MM-DD.txt under the log directory. // Log messages with DEBUG-level and above. // The levels, in order of priorities, are: // EMERG, ALERT, CRIT, ERR, WARN, NOTICE, INFO, DEBUG and OFF // Setting at DEBUG level logs all messages with DEBUG and above. // Set to OFF to turn-off logging. $log = KLogger::instance($logDir, KLogger::DEBUG); // Try vary the level $log->logInfo('Info Test'); $log->logNotice('Notice Test'); $log->logWarn('Warn Test'); $log->logError('Error Test'); // ERR $log->logFatal('Fatal Test'); $log->logAlert('Alert Test'); $log->logCrit('Crit test'); $log->logEmerg('Emerg Test'); // Can also log arrays, objects and NULL, as second parameter $args1 = array('a' => array('b' => 'c'), 'd'); $args2 = NULL; $log->logInfo('Testing passing an array or object', $args1); $log->logWarn('Testing passing a NULL value', $args2); echo 'DONE'; ?>
A log entry contains the timestamp, log-level and message.
LAMP Webapp Security
Sanitize User Inputs against SQL Injection
Use prepared statements instead of regular statement, to prevent SQL injection. See PDO example 3.
Sanitize User Inputs against Cross-Site Scripting (XSS)
Using htmlentities() function
The htmlentities($str)
replaces special characters with their HTML entity, e.g., <
for <
, >
for >
, "
for "
.
The htmlentities($str, ENT_QUOTES)
also replaces single quote to ', which is recommended.
For example,
$str = <<<_END This is a 'quoted' "<b>string</b>" _END; var_dump(htmlentities($str)); // output: This is a 'quoted' "<b>string</b>" var_dump(htmlentities($str, ENT_QUOTES)); // output: This is a 'quoted' "<b>string</b>"
Using HTMLPurifier Library
Check http://htmlpurifier.org.
Where and How to Store MySQL Password (and Configuration Data) used by PHP
To create a database connection in PHP, you need the username/password of a MySQL account (as seen in the above examples). There is no other way but to store database user credentials (most likely in plain text) in a configuration file. This is not just with PHP, but pretty much every language. But, I am certain that you are concern with the security!
- Save them in a configuration file called "
DBConfig.inc.php
" outside the web document root. That is, no direct access for the web users, but PHP scripts can include the file.
Use.php
as file extension (not.inc
or.xml
) - so that the file is parsed by the PHP processor if it ever exposes.
Use.inc.php
for all include files, and configure Apache to deny access to all*.inc.php
by including the following directive in virtual host configuration or.htaccess
(The directiveFilesMatch
accepts an regular expression for filename matching):<FilesMatch "\.inc\.php$"> Order deny,allow Deny from all </FilesMatch>
[Disable directory listing for production Apache system.] - The file shall be readable by
www-data
, which runs the Apache (and the PHP scripts), read-write by developers; BUT NOT world-readable. That is, this file is ultimately protected by the underlying operating system. The only way that people can view the file is actually hack into the server?! - Check for a handshaking token before delivering the include file contents, for example,
// In config files, e.g., 'DBConfig.inc.php' if (!empty('SECURE_INCLUDE_TOKEN')) die('Access error'); ...... // In master files define('SECURE_INCLUDE_TOKEN', 1); include('DBConfig.inc.php'); ......
- Do not store credentials as global variables, who live longer than necessary. In addition, do not return them directly. Instead, define them as local variables in a function which returns a PDO object upon request (the PHP scripts need a PDO connection, not the user credentials). Unset them after use. For example, [TODO]
- Define different MySQL accounts (hostname/username/password) for different roles, with least privileges. You can fine tune the privilege up to table/column-level. Restrict these accounts from only the required hostname (localhost if it is running on the same machine). Don't use these account for other purposes. Needless to say, don't use
root
.CREATE USER 'user'@'localhost' identified by 'xxxx'; GRANT SELECT, UPDATE(isActive) ON test.Users to 'user'@'localhost'; // user@localhost can SELECT (all columns) from the Users table, // UPDATE only isActive column, No INSERT, DELETE, etc.
- For highly secured systems: Store credentials in a password-protected file, in such a way that the administrator needs to enter a password to retrieve its content during start up.
Storing Users' Password in MySQL
References:
- http://alias.io/2010/01/store-passwords-safely-with-php-and-mysql/.
- http://stackoverflow.com/questions/4795385/how-do-you-use-bcrypt-for-hashing-passwords-in-php
Don't
- Don't store password in plain text - obvious!
- Don't invent your own password security algorithm, use an established one.
- Don't encrypt passwords. Anyone with access to your code can find the decryption key.
- Simple hash don't work - because they are susceptible to dictionary attack and brute force attack. It is quite easy to make a list of millions of hashed (a rainbow table) and compare the hashes to recover the password (or one that matches the hash).
- Add salt to the hash, but don't use the same salt for all passwords - attacker can generate another set of rainbow tables.
- Don't use a weak and fast hashing algorithm such as MD5 and SHA. MD5 is even less secure than SHA, because MD5 is prone to collision, thus easier to find a match for a hash. Fast hashing algorithm is susceptible to brute force attack, as the attacker can also generate the hash dictionary fast.
Do
- Use a strong hashing function like bcrypt in PHP.
- Use a random salt for each password.
- Use a slow hashing algorithm to make brute force attacks practically impossible (because the attacker also needs times to run the hashing algorithm, even worst if checking millions of hashes).
- For bonus points, regenerate the hash every time a users logs in, with the same password, by using different salt or different hash strength.
Using bcrypt in PHP
bcrypt
is an hashing algorithm which uses salt, and is scalable with hardware, via a configurable number of rounds. The random salt, multiple rounds and "slowness" can effectively prevent dictionary and brute-force attack. bcrypt
uses the Eksblowfish algorithm, which is a one-way hashing algorithm. You cannot retrieve the plain-text password without knowing the salt, rounds and key (password).
PHP 5.5 - password_hash()/password_verify()
PHP 5.5 introduces new functions called password_hash()
and password_verify()
to create and verify a bcrypt
hash. Read the PHP manual on password_hash()
and password_verify()
. For example,
// Generating hash password $options = array('cost' => 11); // Set the cost, use default salt $pwHash = password_hash('rasmuslerdorf', PASSWORD_BCRYPT, $options); // Use bcrypt hashing algorithm with salt and cost. echo $pwHash; // The generated hash contains the algorithm, salt, and cost. // Verifying hash password $inputPassword = .... $storeHash = .... // Contain the algorithm, salt, and cost if (password_verify($inputPassword, $storeHash)) { echo 'valid!'; } else { echo 'Invalid!'; }
Using crypt() function
The crypt($str, $salt)
takes two parameters, the input string and a salt. The salt is also used to identify the hashing algorithm. For blowfish, the salt shall be in this format: $2a$dd$....
where 2a
specifies blowfish, dd is a two-digit cost (from 04 to 31), and .... are 22 alphanumeric characters for salt. Extra characters after the salt are ignored.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
<?php /** * Testing bcrypt password. */ // Generating Password Hash $password = 'rasmuslerdorf'; $cost = 10; // Create a random salt $salt = strtr(base64_encode(mcrypt_create_iv(16, MCRYPT_DEV_URANDOM)), '+', '.'); echo $salt, '<br />'; // psYGmg2b5s60WDwfrawQ4g== // First 22 characters are psYGmg2b5s60WDwfrawQ4. The rest are ignored. // Prefix salt with blowfish header $2a$dd$. $salt = sprintf("$2a$%02d$", $cost) . $salt; echo $salt, '<br />'; // $2a$10$psYGmg2b5s60WDwfrawQ4... // Hash the password with the salt $pwHash = crypt($password, $salt); echo $pwHash, '<br />'; // $2a$10$psYGmg2b5s60WDwfrawQ4eThJYeC0esqWNS.KdIKZsCil3gk/BCnS // Verifying User Input Password $inputPassword = 'rasmuslerdorf'; // Try an invalid password $storeHash = $pwHash; if (crypt($inputPassword, $storeHash) === $storeHash) { // Use the same function for verification // $storeHash has the blowfish header and 22 character salt. echo 'Valid!'; } else { echo 'Invalid!'; } ?> |
Sending Email
Use the PHP mail()
function to send email. You can test the mail()
without setting up a mail server by configuring php.ini
as follows:
$ sudo gedit /etc/php5/apache2/php.ini
......
[mail function]
sendmail_path = tee mail.out > /dev/null
......
// Restart Apache
$ sudo service apache2 restart
The above directs the email to the file mail.out
Try running the following script to send an email via the mail()
<?php mail('test@example.com', 'the subject', 'the body', "From: noreply@nowhere.com \r\n"); ?>
To send actual email, you need to send up a Mail Server (such as postfix). Read "How to set up postfix Mail Server on Ubuntu"
Edit php.ini
:
$ sudo gedit /etc/php5/apache2/php.ini
......
[mail function]
sendmail_path = "/usr/sbin/sendmail -t -i"
......
// Restart Apache
$ sudo service apache2 restart
You can also leave it to the default.
A Webapp Case Study
Logging in/out and User Management
Shopping Cart
REFERENCES & RESOURCES
- PHP mother site @ http://www.php.net; "PHP Manual" @ http://php.net/manual/en/index.php; "PHP Language Reference" @ http://www.php.net/manual/en/langref.php.
- PHP manual "mysqli quick start guide" @ http://www.php.net/manual/en/mysqli.quickstart.php.
- PHP manual "PHP Data Objects (PDO)" @ http://www.php.net/manual/en/book.pdo.php.
- PHP manual "MySQL Driver and Plugin" @ http://www.php.net/manual/en/set.mysqlinfo.php.