62

Access to a database

Protect a form with a captcha  |  Monitoring visits

Create site11 by copying site10.

  1. /cms
    1. ...
    2. site10
    3. site11

In this chapter, we are going to program the access to a database, the configuration of the connector and the initialization of the connection when the program starts, then a set of functions for registering variables in the DB.

To test the result online, enter http://www.frasq.org/cms/site11 in the address bar of your navigator.

Create a user called frasqdb2 in the table user of the database mysql:

INSERT INTO `user`
(Host, `User`, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, ssl_type, max_questions, max_updates, max_connections, max_user_connections)
VALUES ('localhost', 'frasqdb2', PASSWORD('Fch9Xw4k'), 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '0', '0', '0', '0')

IMPORTANT: Choose a random password and write it down. Remember to select the PASSWORD function for the field Password or the value will not be encrypted and the password transmitted by PHP, which will be encrypted, will not match.

Add the access rights for the user frasqdb2 to the DB frasqdb2 in the db table:

INSERT INTO `db`
(Host, Db, USER, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, Lock_tables_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Execute_priv, Event_priv, Trigger_priv)
VALUES ('localhost', 'frasqdb2', 'frasqdb2', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N')

Quit MySQL:

mysql> quit

Read the article The web developer tools to learn how to install MySQL and more generally a LAMP environment.

db.inc

A connector to a DB has 4 parameters: the name of the user of the DB and its password, the name or the IP address of the server and the name of the DB.

Add the configuration file db.inc in the folder includes with the following content:

  1. /cms/site11
    1. includes
      1. db.inc
  1. $db_url = 'mysql://username:password@localhost/databasename';
  2. $db_prefix = false;
  3. $db_debug = false;

$db_url defines the connector the DB. Replace databasename and username by frasqdb2, password by the password you wrote down when you created the user of the DB.

$db_prefix allows changing the names of all the tables in order to avoid conflicts with other components of the DB. $db_debug set to true traces all the SQL orders.

IMPORTANT: Make sure this file is protected from an external access and yet readable by Apache:

$ chmod 600 db.inc
$ sudo chown www-data db.inc

Initialize the connection to the DB when the program starts in the bootstrap function defined in the file bootstrap.php in the folder library:

bootstrap.php
  1.     @include 'db.inc';

Loads the configuration of the connexion to the database.

  1.     if (isset($db_url) && $db_url == 'mysql://username:password@localhost/databasename') {
  2.         $db_url = false;
  3.     }
  4.  
  5.     if ($db_url) {
  6.         require_once 'pdo.php';
  7.         db_connect($db_url);
  8.     }

Sets $db_url to false if $db_url has the value which hints to the parameters in a connector. If $db_url isn't false, loads the file with the functions for accessing a DB then opens the connection.

Add the file pdo.php in the folder library with the following content:

  1. /cms/site11
    1. library
      1. pdo.php
pdo.php
  1. $db_conn=false;
  2. $db_scheme=false;
  3.  
  4. function db_connect($url, $persistent=true) {
  5.     global $db_conn, $db_scheme;
  6.  
  7.     $url = parse_url($url);
  8.  
  9.     $scheme = $url['scheme'];
  10.     $host = urldecode($url['host']);
  11.     if (isset($url['port'])) {
  12.         $host = $host . ':' . $url['port'];
  13.     }
  14.     $user = urldecode($url['user']);
  15.     $pass = isset($url['pass']) ? urldecode($url['pass']) : '';
  16.     $path = urldecode($url['path']);
  17.     if ($path[0] == '/') {
  18.         $path = substr($path, 1);
  19.     }
  20.  
  21.     $dsn = "$scheme:host=$host;dbname=$path";
  22.     $options = array(PDO::ATTR_PERSISTENT => $persistent ? true : false);
  23.  
  24.     try {
  25.         $db_conn = new PDO($dsn, $user, $pass, $options);
  26.         $db_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  27.         $db_conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
  28.         $db_conn->exec("SET NAMES 'utf8'");
  29.  
  30.         if ($scheme == 'mysql') {
  31.             $db_conn->exec("SET SQL_MODE='ANSI_QUOTES'");
  32.         }
  33.  
  34.         $db_scheme=$scheme;
  35.     }
  36.     catch (PDOException $e) {
  37.         die($e->getMessage());
  38.     }
  39.  
  40.     return $db_conn;
  41. }

db_connect parses $url to extract the parameters for the connection to the DB then opens a permanent connection with the server and selects the requested DB. In case of a problem, db_connect pulls up the MySQL error and triggers a PHP error. Before returning the connector, db_connect forces the character encoding to UTF-8. Note that $db_conn is a global variable.

  1. function db_query($sql) {
  2.     global $db_debug;
  3.     global $db_conn;
  4.  
  5.     if ($db_debug) {
  6.         dump($sql);
  7.     }
  8.  
  9.     try {
  10.         $r = $db_conn->query($sql);
  11.     }
  12.     catch (PDOException $e) {
  13.         die($e->getMessage());
  14.     }
  15.  
  16.     $rows = $r->fetchAll(PDO::FETCH_ASSOC);
  17.  
  18.     if (!$rows) {
  19.         return false;
  20.     }
  21.  
  22.     return $rows;
  23. }

db_query executes the request $sql by calling the private function _db_sql_query and returns all the response lines in an array or false if the request didn't return anything.

  1. function db_query($sql) {
  2.     global $db_debug;
  3.     global $db_conn;
  4.  
  5.     if ($db_debug) {
  6.         dump($sql);
  7.     }
  8.  
  9.     try {
  10.         $r = $db_conn->query($sql);
  11.     }
  12.     catch (PDOException $e) {
  13.         die($e->getMessage());
  14.     }
  15.  
  16.     $rows = $r->fetchAll(PDO::FETCH_ASSOC);
  17.  
  18.     if (!$rows) {
  19.         return false;
  20.     }
  21.  
  22.     if (get_magic_quotes_runtime()) {
  23.         foreach ($rows as $row) {
  24.             foreach ($row as $k => &$v) {
  25.                 $v = stripslashes($v);
  26.             }
  27.         }
  28.     }
  29.  
  30.     return $rows;
  31. }

In this version used before PHP 7, db_query removes all the \ (BACKSLASH) from all the values which are automatically added by PHP if the configuration parameter magic_quotes_gpc is true.

  1. function db_insert($sql) {
  2.     return _db_sql_exec($sql);
  3. }
  4.  
  5. function db_update($sql) {
  6.     return _db_sql_exec($sql);
  7. }
  8.  
  9. function db_delete($sql) {
  10.     return _db_sql_exec($sql);
  11. }
  12.  
  13. function db_exec($sql) {
  14.     return _db_sql_exec($sql);
  15. }
  16.  
  17. function db_insert_id($id=null) {
  18.     global $db_conn;
  19.  
  20.     $r = $db_conn->lastInsertId($id);
  21.  
  22.     return $r;
  23. }

db_insert, db_update and db_delete return the result of the execution of the request $sql by the private function _db_sql_query. db_insert_id returns the primary key created by the last call to db_insert.

  1. function db_sql_arg($s, $escape=true, $optional=false) {
  2.     global $db_conn;
  3.  
  4.     if ($s === NULL or $s === false or $s === '') {
  5.         return $optional ? 'NULL' : "''";
  6.     }
  7.  
  8.     return $escape ? $db_conn->quote($s) : "'$s'";
  9. }

db_sql_arg prepares an argument for an SQL request by adding a ' (QUOTE) before and after a literal value. Set $escape to false only if $s cannot contain characters which are special to SQL. Set $optional to true for a field whose value can be optional.

IMPORTANT: PHP must be connected to a DB for the mysql_real_escape_string to function.

  1. function db_prefix_table($table) {
  2.     global $db_prefix;
  3.  
  4.     return $db_prefix ? $db_prefix . $table : $table;
  5. }

db_prefix_table adds $db_prefix at the beginning of a table name. $db_prefix is defined in db.inc.

IMPORTANT: Systematically use db_prefix_table and db_sql_arg to build table names and argument values in an SQL request. EXAMPLE:

  1.     $sqlname=db_sql_arg($name, false);
  2.  
  3.     $tabregistry=db_prefix_table('registry');
  4.  
  5.     db_delete("DELETE FROM $tabregistry WHERE name=$sqlname LIMIT 1");

$sqlname contains the value of $name surrounded by quotes, without escape characters. If $db_prefix is set to fq_, $tabregistry will be set to fq_registry.

  1. function _db_sql_exec($sql) {
  2.     global $db_debug;
  3.     global $db_conn;
  4.  
  5.     if ($db_debug) {
  6.         dump($sql);
  7.     }
  8.  
  9.     try {
  10.         $r = $db_conn->exec($sql);
  11.     }
  12.     catch (PDOException $e) {
  13.         die($e->getMessage());
  14.     }
  15.  
  16.     return $r;
  17. }

_db_sql_exec traces the request $sql if $db_debug is true then executes it. In case of error, PHP is stopped.

_db_sql_exec is a private function called by all the functions which execute an SQL request. It allows centralizing all the calls to the exec method of the PDO class. $db_debug is defined in db.inc.

Modify the home page, in English and in French, to display the version numbers of PHP, MySQL and the web server's system:

  1. /cms/site11
    1. views
      1. en
        1. home.phtml
      2. fr
        1. home.phtml
home_en.phtml
  1. <h3>Welcome</h3>
  2. <p>PHP <?php echo phpversion(); ?><br />
  3. MySQL <?php echo db_version(); ?><br />
  4. <?php echo php_uname('s'); ?>&nbsp;<?php echo php_uname('r'); ?></p>
home_fr.phtml
  1. <h3>Bienvenue</h3>
  2. <p>PHP <?php echo phpversion(); ?><br />
  3. MySQL <?php echo db_version(); ?><br />
  4. <?php echo php_uname('s'); ?>&nbsp;<?php echo php_uname('r'); ?></p>

Enter http://localhost/cms/site11 in the address bar of your navigator to check that the site is connected to the DB.

Set $db_url to false in the file includes/db.inc to not connect the program to the DB. Reload the home page. An error is generated. The function db_version isn't defined. The file pdo.php hasn't been loaded by the function bootstrap.

To illustrate the access functions to a DB, we will program a permanent register for saving variables and their values:

$ mysql -u root -p

Enter in the BD of the site which you have just created:

mysql> USE frasqdb2;

Create the table registry with the fields name and value:

mysql> CREATE TABLE registry (
  name varchar(100) NOT NULL,
  value longtext NOT NULL,
  PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Add the file registry.php in the folder library with the following content:

  1. /cms/site11
    1. library
      1. registry.php
registry.php
  1. function registry_get($name, $default=false) {
  2.     $sqlname=db_sql_arg($name, false);
  3.  
  4.     $tabregistry=db_prefix_table('registry');
  5.  
  6.     $r = db_query("SELECT value FROM $tabregistry WHERE name=$sqlname LIMIT 1");
  7.  
  8.     return $r ? unserialize($r[0]['value']) : $default;
  9. }

registry_get returns the value of the variable whose name is given by $name. Values registered in the DB are serialized. registry_get builds an SQL order which extracts the value of the requested variable and deserialize it before returning it. Note how the functions db_sql_arg and db_prefix_table are used. If the variable $name doesn't exist, registry_get returns the value given by $default which is false by default.

  1. function registry_set($name, $value) {
  2.     $sqlname=db_sql_arg($name, false);
  3.     $sqlvalue=db_sql_arg(serialize($value), true);
  4.  
  5.     $tabregistry=db_prefix_table('registry');
  6.  
  7.     db_insert("INSERT $tabregistry SET name=$sqlname, value=$sqlvalue ON DUPLICATE KEY UPDATE name=VALUES(name), value=VALUES(value)");
  8. }

registry_set registers the variable whose name is given by $name with the value given by $value. Values registered in the DB are serialized. If the variable is already registered, its value is replaced.

  1. function registry_delete($name) {
  2.     $sqlname=db_sql_arg($name, false);
  3.  
  4.     $tabregistry=db_prefix_table('registry');
  5.  
  6.     db_delete("DELETE FROM $tabregistry WHERE name=$sqlname LIMIT 1");
  7. }

registry_delete deletes the variable whose named is given by $name.

site11.zip

Comments

Your comment:
[p] [b] [i] [u] [s] [quote] [pre] [br] [code] [url] [email] strip help 2000

Enter a maximum of 2000 characters.
Improve the presentation of your text with the following formatting tags:
[p]paragraph[/p], [b]bold[/b], [i]italics[/i], [u]underline[/u], [s]strike[/s], [quote]citation[/quote], [pre]as is[/pre], [br]line break,
[url]http://www.izend.org[/url], [url=http://www.izend.org]site[/url], [email]izend@izend.org[/email], [email=izend@izend.org]izend[/email],
[code]command[/code], [code=language]source code in c, java, php, html, javascript, xml, css, sql, bash, dos, make, etc.[/code].