Escaping characters From MySQL to PHP

We will have a small example demonstrating how to escape characters. There are some characters which are considered to be unsafe in a database environment. One of them is a single quote character.

mysql> CREATE TABLE IF NOT EXISTS Authors(Id INT PRIMARY KEY AUTO_INCREMENT, 
    ->     Name VARCHAR(25)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

For the example, we create an Authors table.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb"; 

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$name = "O'Neill";
$name_es = mysql_real_escape_string($name);

$query = "INSERT INTO Authors(Name) VALUES('$name_es')";
$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 

mysql_close();

?>

We insert a new author to the Authors table. The name of the author is O'Neill. The name has an unsafe single quote character.

$name_es = mysql_real_escape_string($name);

Thay is why we use the mysql_real_escape_string() function to escape this character.

$query = "INSERT INTO Authors(Name) VALUES('$name_es')";
$rs = mysql_query($query);

We create the statement and execute it.

mysql> SELECT * FROM Authors;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | O'Neill |
+----+---------+
1 row in set (0.00 sec)

The name has been successfully written to the table.

Column headers

Next we will show, how to print column headers with the data from the database table.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb"; 

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$query = "SELECT * From Cars LIMIT 8";
    
$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 

$cname1 = mysql_fetch_field($rs, 0);
$cname2 = mysql_fetch_field($rs, 1);
$cname3 = mysql_fetch_field($rs, 2);

printf("%3s %-11s %8s\n", $cname1->name, $cname2->name, 
    $cname3->name);

while ($row = mysql_fetch_row($rs)) {
    printf("%3s %-11s %8s\n", $row[0], $row[1], $row[2]);
}

mysql_close();

?>

Again, we print the contents of the Writers table to the console. Now, we include the names of the columns too.

$cname1 = mysql_fetch_field($rs, 0);
$cname2 = mysql_fetch_field($rs, 1);
$cname3 = mysql_fetch_field($rs, 2);

To get a specific field name, we utilize the mysql_fetch_field() function. The function return an object containing column information.

printf("%3s %-11s %8s\n", $cname1->name, $cname2->name, 
    $cname3->name);

The column names are printed and formatted. The name property contains the column name.

$ php columns.php
Connection established
Database selected
Query: SELECT * From Cars LIMIT 8 executed
 Id Name           Price
  1 Audi           52642
  2 Mercedes       57127
  3 Skoda           9000
  4 Volvo          29000
  5 Bentley       350000
  6 Citroen        21000
  7 Hummer         41400
  8 Volkswagen     21600

Ouput of the script.

Fields, rows

The following script counts the number of fields/columns and rows returned by a query.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$query = "SELECT * FROM Cars WHERE Id IN (1, 2, 3)";

$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 

echo "We have " . mysql_num_fields($rs) . " fields\n";
echo "We have " . mysql_num_rows($rs) . " rows\n";

print_r(mysql_fetch_row($rs));

mysql_close();

?>

We select three rows from the Cars table. We count the number of rows and columns returned by a query.

$query = "SELECT * FROM Cars WHERE Id IN (1, 2, 3)";

This is the query to be executed. It selects first three rows from the Cars table.

echo "We have " . mysql_num_fields($rs) . " fields\n";

The mysql_num_fields() returns the number of fields returned by a query.

echo "We have " . mysql_num_rows($rs) . " rows\n";

The mysql_num_rows() returns the number of rows returned by a query.

print_r(mysql_fetch_row($rs));

We print the contents of the array.

$ php fields_rows.php
Connection established
Database selected
Query: SELECT * FROM Cars WHERE Id IN (1, 2, 3) executed
We have 3 fields
We have 3 rows
Array
(
    [0] => 1
    [1] => Audi
    [2] => 52642
)

Running the script.

Writing images

Some people prefer to put their images into the database, some prefer to keep them on the file system for their applications. Technical difficulties arise when we work with millions of images. Images are binary data. MySQL database has a special data type to store binary data called BLOB (Binary Large Object).

mysql> CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);
Query OK, 0 rows affected (0.06 sec)

For this example, we create a new table called Images.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$file = "woman.jpg";

$img = fopen($file, 'r');

if (!$img) {
    echo "Cannot open file for writing\n";
    trigger_error("Cannot open file for writing\n", E_USER_ERROR);
} 

$data = fread($img, filesize($file));

if (!$data) {
    echo "Cannot read image data\n";
    trigger_error("Cannot read image data\n", E_USER_ERROR);
}

$es_data = mysql_real_escape_string($data);
fclose($img);

$query = "INSERT INTO Images(Id, Data) Values(1, '$es_data')";
    
$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query successfully executed\n";
} 

mysql_close();

?>

In the above script, we read a jpg image and insert it into the Images table.

$file = "woman.jpg";

This is the image name, that we read from the filesystem and write into the database. It is located in the same directory as the script name.

$img = fopen($file, 'r');

if (!$img) {
    echo "Cannot open file for writing\n";
    trigger_error("Cannot open file for writing\n", E_USER_ERROR);
} 

$data = fread($img, filesize($file));

if (!$data) {
    echo "Cannot read image data\n";
    trigger_error("Cannot read image data\n", E_USER_ERROR);
}

We open and read the image. The fread()function returns the data as string.

$es_data = mysql_real_escape_string($data);

We escape unsafe characters.

fclose($img);

We close the handle to the image file.

$query = "INSERT INTO Images(Id, Data) Values(1, '$es_data')";
    
$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query successfully executed\n";
} 

We insert the data to the newly created Images table.

Reading images

In the previous example, we have inserted an image into the database table. Now we are going to read the image back from the table.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$query = "SELECT Data FROM Images WHERE Id=1";
    
$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 

$row = mysql_fetch_row($rs);

$file = "woman2.jpg";

$img = fopen($file, 'wb');

if (!$img) {
    echo "Cannot open file for writing\n";
    trigger_error("Cannot open file for writing\n", E_USER_ERROR);
} 

$r3 = fwrite($img, $row[0]);

if (!$r3) {
    echo "Cannot write image to file\n";
    trigger_error("Cannot write image to file\n", E_USER_ERROR);
} 

fclose($img);

mysql_close();

?>

We read one image from the Images table.

$query = "SELECT Data FROM Images WHERE Id=1";

We select one record from the table.

$row = mysql_fetch_row($rs);

We fetch one row from the result set. There is only one row, containing the image data.

$file = "woman2.jpg";

We will create a new image file name called "woman2.jpg".

$img = fopen($file, 'wb');

if (!$img) {
    echo "Cannot open file for writing\n";
    trigger_error("Cannot open file for writing\n", E_USER_ERROR);
} 

We open a writable binary file.

$r3 = fwrite($img, $row[0]);

if (!$r3) {
    echo "Cannot write image to file\n";
    trigger_error("Cannot write image to file\n", E_USER_ERROR);
} 

We write the data to the filesystem using the fwrite()function.
Now we should have an image called "woman2.jpg" in our current directory. We can check if it is the same image, that we have inserted into the table.