Data Security

This post identifies the minimum privileges required by a user in MySQL to take a backup of a database. The mysqldump backup tool takes a read consistent view of the database. As a result, it needs to lock the tables and select the data from each table in a database. As a result, SELECT and LOCK TABLES privileges are required.

Here is an example below. We are logged in with root user and we will create a separate user in this database, “demo” in our case, that will allows us to perform the backups:

First we create the user

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1049
Server version: 5.0.45 SUSE MySQL RPM

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from mysql.user where user = 'backup';
Empty set (0.00 sec)

mysql> grant select, lock tables, show view on demo.* to backup@localhost identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.user where user = 'backup';
+-----------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| 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 | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
+-----------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost | backup | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | 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 |
+-----------+--------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
1 row in set (0.00 sec)

mysql> quit
Bye

Now we run the backup:

$ mysqldump -u backup -ptest demo > demo.sql
$ more demo.sql
-- MySQL dump 10.11
--
-- Host: localhost    Database: demo
-- ------------------------------------------------------
-- Server version	5.0.45

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `Test`
--

DROP TABLE IF EXISTS `Test`;
CREATE TABLE `Test` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(10) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Test`
--

LOCK TABLES `Test` WRITE;
/*!40000 ALTER TABLE `Test` DISABLE KEYS */;
/*!40000 ALTER TABLE `Test` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Temporary table structure for view `Test_view`
--

DROP TABLE IF EXISTS `Test_view`;
/*!50001 DROP VIEW IF EXISTS `Test_view`*/;
/*!50001 CREATE TABLE `Test_view` (
  `a` int(11),
  `b` int(11)
) */;

If I write all my SQL statements in PHP code is this ideal for performance?

My first question in response to this is what is considered “ideal” for performance? Having worked as a technical performance specialist for a variety of companies, we need to look more closely at what is considered adequate performance. The true measure of performance is the time it takes to generate a report, respond to a query and so on (response times). Each one of these have different expectations on response times. For example, having a report processing millions of records in a database to summarize is expected to take several seconds/minutes and in some cases hours to complete. However, an online transaction processing (OLTP) solution cannot have the end user waiting minutes or hours to complete the purchase of a product.

Expectations should be clearly drawn up before working on performance related issues.

Before working on performance related issues you need to understand the architecture that you are dealing with and understanding how your application interacts with the hosts and the server processes.

Architecture

Recently I have been developing applications with databases (MySQL) and PHP. Your server architecture will play a huge role in how your application should be developed. For example, some hosting companies provide both the web server and the database server on the same machine. Access to the database is made via internal memory structures which are quick. However, some hosting companies separate the web servers from the databases and as a result applications need to establish a connection to the database and then send the database SQL statements across the network. This can significantly slow down the application.

An application connected to a database has two very important measurements:

• Response time
• Throughput

The response time is the time lag between an application sending a request and receiving a response. For example, picture a water pipe. You put water in at one end and you time the water arriving at the other end. This is your half your response time. It does not matter how much water you put in it will still arrive at the same time. The response time is directly proportional to the length of the pipe i.e. the distance it needs to cover.

Throughput on the other hand is the size of the pipe. Imagine a straw sized pipe. The amount of water reaching the other end is limited by the volume of water contained by the pipe. The larger the pipe the more water will pour through. This is your throughput. The time it takes to pour the water through on both pipes is still the same.

Localhost

Localhost is a synonym for a local based connection to the server process. This is very quick as access is typically via memory. In cases where the database server is on the same machine as the application, the pipe analogy above would give us a very large pipe with both ends close together.

Host

In cases where the database server exists on a separate host than the application then there are several things you need to consider:

• The response time between sending a request and receiving the response
• The throughput of data available from the database server

Imagine that a response time of 100ms between sending a message across the network and received by the database. The database processes the information and sends its response back to the calling application. This may seem like a small amount of time, but what happens, as many applications do, it sends another SQL statement based on the response from the first query. Several 100ms roundtrips add up very quickly.

Likewise, imagine a lot of data is either sent to the database server or received back from the database server. Imagine a 3Mbps (mega bits per second) connection (this translates to approximately 300 kilobytes per second). To transfer 1MB of data will take approximately 3 seconds. If the database server exists in the same data center as the application host it typically has a 1Gbps connection (or approximately 100Mbytes per second) which is often advertised. This can be quite misleading because all traffic inside the data center will use this 1Gbps connection and your application will only have a share of this.

If database server and application exist on different machines is there anything that can be done to improve performance?

Indeed there is. Database servers have been designed to allow stored procedures to be created and executed. If you shift the work from the application into the database then you can reduce substantially the response times and the data transfer between the application and database.

Consider the following scenario. You are building logic into the application whereby you are attempting to add a new contact into your database. To avoid duplicate entries you want to first verify that the contact name does not already exist in the database. If it does you then update the remainder of the information i.e. perform and update. If there is not existing entry then you insert a new record into the database. Without using stored procedures you would have the application send the SQL statement (does contact record already exist) across the network to the database. The database then responds back to the application that it either exists or it does not. It then makes a decision based on the response from the database and either generates an INSERT SQL statement or an UPDATE SQL statement. In this case two statements are transferred over the network, parsed by the database server and then executed. The results are then sent back and more SQL statements are executed. In the case of a stored procedure a call is made to the stored procedure which has already been parsed and simply executes. Upon completion of execution it then returns a response to the calling application. This can significantly improve performance, especially if the response times are large and the volume of data exchanged is large which is used to make some form of decision.

Another advantage of using stored procedures for any UPDATE, DELETE or INSERT statements is that all the applications business logic is maintained within the database. The business logic is the logic that is followed when data is being added into a database.

On the flip side, maintaining code at two locations for an application can become complex, the application code and the database stored procedures. Their interactions need to be fully understood as a change in one location can have substantial implications in the others.

Look out for further whitepapers on developing database stored procedures and other performance enhancement tips and tricks.

Ronan Cashell is a Senior Database Administrator/Technical Project manager with over 20 years of experience working in IT both as a database administrator and a UNIX/Linux administrator. He is a certified Oracle DBA and certified Linux administrator.

Many people have asked if there was a way to have both their private Dropbox account and their company Dropbox account co-existing under the same Mac OS X login.

I have put together the steps involved along with an automator process which you can download below.

Before we start consider the following:

• Each dropbox account will have its own running process
• Each dropbox account should have its own unique account login
• Each dropbox process should have its own dedicated “Dropbox” folder

In my example, I will be showing how this works using a Mac OS X machine. I have 2 separate dropbox accounts and have created two separate Dropbox folders. The first account will keep the default /Users/macosuser/Dropbox folder. The second account will use /Users/macosuser/emailaddress as its Dropbox folder. Under this folder, Dropbox will automatically create a folder called Dropbox.

Start a Terminal session allowing a command line to be typed in.

The first thing we need to do is to change the location of the HOME environment variable. This is pointing by default to /Users/macosuser where macosuser is your username on the Mac. If we change that to /Users/macosuser/account for example:

Last login: Mon Nov  7 19:00:19 on ttys004
macbook:~ macosuser$ export HOME=/Users/macosuser/test/
macbook:~ macosuser$ mkdir $HOME
macbook:~ macosuser$ /Applications/Dropbox.app/Contents/MacOS/Dropbox &
[1] 22774
bash-3.2$

If this is the first time you are running this you will be prompted to either configure a new account or an existing account for Dropbox.After each reboot of the machine you will need to manually restart the Dropbox as explained above. However, once the account is configured you will no longer be prompted for each restart.

Automated Service

We have also developed a Finder service which allows you to select a folder in Finder and enable the Dropbox Setup which automates the steps above.

If you wish to get a copy of this service please fill in your email address and we will email you a link to the downloadable file. These emails are not passed on to 3rd parties and are used to notify you of changes to “Dropbox Setup” or any new products that we release.

This blog posting is about the importance of escape the escape character in MySQL. The escape character in MySQL is the backslash (\) character.

If you are attempting to add the following text into a database “\frac\{9\}\{5\}”, the insert command will strip the backslashes out before inserting into the table. We will demonstrate this in this blog post.

Consider the following table

CREATE TABLE testtext(
  id INT AUTO_INCREMENT ,
  data TEXT,
  PRIMARY KEY ( id )
) ENGINE = InnoDB;

And then we insert into this table the following text “\frac\{9\}\{5\}”.

mysql> INSERT INTO testtext (data) VALUES ('\frac\{9\}\{5\}');
Query OK, 1 row affected (0.00 sec)

mysql> select * from testtext;
+----+------------+
| id | data       |
+----+------------+
|  1 | frac{9}{5} |
+----+------------+
1 row in set (0.00 sec)

We can see that the backslashes were stripped out during the insert. In order to insert data to maintain the backslashes we need to escape character these values too. So \ will need to become \\. As follows:

mysql> INSERT INTO testtext (data) VALUES ('\\frac\\{9\\}\\{5\\}');
Query OK, 1 row affected (0.02 sec)

mysql> select * from testtext;
+----+-----------------+
| id | data            |
+----+-----------------+
|  1 | frac{9}{5}      |
|  2 | \frac\{9\}\{5\} |
+----+-----------------+
2 rows in set (0.01 sec)

I am getting the following messages in the mysql.log file:

“[Warning] IP address ‘XXX.XXX.XXX.XX’ could not be resolved: no reverse address mapping.”

The reverse address mapping is basically requesting from the DNS, “I have this IP address can you give me a domain name associated with this IP address”. The domain name is needed in the login verification i.e. authentication is based on username and host. The host in this case is not an IP address but a host name.

So for example, if the authentication in the server is myuser@localhost, we need to verify that the person is logged on from localhost.

The person logging in will have an identified IP address but not a domain name, so we need to convert the IP address to a domain name. The domain name is then compared along with the username to verify which entry in the mysql.user table should be used to complete verification.

Shortcodes is a feature added in release 2.5 of wordpress. Shortcodes allows specific features to be added directly into a blog post or page. This differs from a plugin which offers features at a page level or even at a site level.

The syntax for a short code in your blog is

[shortcodename][/shortcodename]

The shortcode executes the PHP code which performs an action at this precise point in the page. A short code may also pass attribute values which corresponds to parameters passed into the function being executed. This offers a richer set of features by allowing the function to behave in a different manner depending on the parameters/attributes being passed in. Here is the syntax of shortcodes with attributes:

[shortcodename attribute1=value1 attribute2=value2][/shortcodename]

In some instances the shortcode may perform some action with the content that exists between the open short code and close short code on the page.

[shortcodename attribute1=value1 attribute2=value2]content[/shortcodename]

Here is an example of how short codes can be used.

Keep tuned here as we will provide step by step instructions on how to write your own short codes that can be added to your pages.

When a web browsers sends a request to a web server, the web server automatically determines based on the file extension what needs to be done with that requested file. A file with the extension .html is streamed back to the web browser that handles and displays the content. However, what the web server is doing automatically is determining based on the file extension the type of content (called MIME type) and sending headers back to the web browser the MIME type. For instance, a request for a .html file has the web server generate a HTTP header with the MIME type “text/html”. The web browser analyses the MIME type to determine what should be done with it.

The example above is quite simple, but what if we request a .pdf document. The web server will return in the HTTP header the MIME type “application/pdf” and the browser determines what to do with the information. In this scenario the browser may startup PDF reader or if PDF reader is not installed will popup a message allowing the file to be saved.

Furthermore, the information sent is a stream of data and not a file. So when a request is sent to a web server for a .pdf document, the web server generates automatically the HTTP header with the correct MIME type and then streams the contents of the file to the web browser. It does not transfer the file itself.

Understanding this is a basic building block for building secure and functional websites.

To have a look at file extensions and their associated MIME types have a look here

I have put together a screencast, explaining two ways in which the root password may be changed on a MySQL server. One solution is specific to Linux, the other is a more generic solution that will work on both Windows and Linux. It is assumed that you have the admin/root user and passwords for the respective operating systems.

This is something that appears but is not solvable using standard SQL. For example, let us imagine that we want to return a set of rows belonging to a set of values. To be more concrete, imagine we have a table of books (defined with bookid and title) and a set of users (uid and username) and booksread (bookid, uid). We want to return a list of persons who has read a particular book or books. The last part is particularly important it may be one or more books. We could do this through our programming language whether PHP, C, C++, Java or Perl where we dynamically create the SQL before submitting it. However, in some cases we are using a tool which permits execution of a stored procedure but not the means of generating SQL. In this case we could use stored procedure and dynamically generated SQL from within the stored procedure based on input parameters.

From our example, let us imagine that we want to return a set of users that have a read a particular books:

SELECT u.username
  FROM users u, books b, booksread r
 WHERE u.uid = r.uid
   AND r.bookid = b.bookid
   AND b.title = 'abc';

This is simple as we have a single value no dynamic SQL is required to resolve this issue. However, what if we have a list of books, such as: ‘abc’,'photography’,'video’ but this list is not restricted to those entries. There may be one or more different entries. In this case the SQL above is not sufficient. We need to generate the SQL dynamically as follows:

SELECT u.username, b.title
  FROM users u, books b, booksread r
 WHERE u.uid = r.uid
   AND r.bookid = b.bookid
   AND b.title IN ( listofbooks );

Where listofbooks is replaced by the following string “‘abc’,'photography’,'video’”.

So we will created a stored procedure that will accept a string list of books:

DROP PROCEDURE IF EXISTS sp_books;
DELIMITER $$
CREATE PROCEDURE sp_books(IN listOfBooks VARCHAR(100))
BEGIN
  SET @query := CONCAT("SELECT u.username, b.title FROM users u, books b, booksread r WHERE u.uid = r.uid AND r.bookid = b.bookid AND b.title IN (", listOfBooks, " )");
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;
$$

Lets have a look at this in action:

mysql> create table books(bookid int auto_increment primary key, title varchar(100));
Query OK, 0 rows affected (0.06 sec)

mysql> create table users(uid int auto_increment primary key, username varchar(30));
Query OK, 0 rows affected (0.07 sec)

mysql> create table booksread(uid int, bookid int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into users(username) values ('Tom'),('Dick'),('Harry'),('Jerry'),('Frank'),('Ron'),('Bud');
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> insert into books(title) values ('abc'),('photography'),('video'),('soccer'),('football'),('tennis'),('golf'),('fishing'),('cycling');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> insert into booksread(uid, bookid) values (1,2),(1,5),(2,4),(3,2),(3,3),(3,4),(3,5),(5,1),(5,5);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

If we use the SQL above we will get back:

mysql> SELECT u.username, b.title
    ->   FROM users u, books b, booksread r
    ->  WHERE u.uid = r.uid
    ->    AND r.bookid = b.bookid
    ->    AND b.title IN ( 'abc','photography','video');
+----------+-------------+
| username | title       |
+----------+-------------+
| Tom      | photography |
| Harry    | photography |
| Harry    | video       |
| Frank    | abc         |
+----------+-------------+
4 rows in set (0.00 sec)

So when creating and executing the stored procedure we would expect similar results:

mysql> CREATE PROCEDURE sp_books(IN listOfBooks VARCHAR(100))
    -> BEGIN
    ->   SET @query := CONCAT("SELECT u.username, b.title FROM users u, books b, booksread r WHERE u.uid = r.uid AND r.bookid = b.bookid AND b.title IN (", listOfBooks, " )");
    ->   PREPARE stmt FROM @query;
    ->   EXECUTE stmt;
    ->   DEALLOCATE PREPARE stmt;
    -> END;
    -> $$
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> call sp_books( "'abc','photography','video'" );
+----------+-------------+
| username | title       |
+----------+-------------+
| Tom      | photography |
| Harry    | photography |
| Harry    | video       |
| Frank    | abc         |
+----------+-------------+
4 rows in set (0.04 sec)

Query OK, 0 rows affected (0.04 sec)

There are some things to note:

• The list of entries passed into the stored procedure must contain a surrounding “” with the strings inside containing a single quote ” or escape the double quotes inside the quotes.
• If you attempt to declare a local varchar so store the query used in the PREPARE statement this will fail. This should be stored inside a user defined session variable i.e. @query.

It should be noted that anyone who needs this type of solution should look closely at their data model and have this corrected.

A question was raised where a field in the table contains a comma separated list of entries. These entries should be accessible. Through a set of queries. Let us call these comma separated values attributes. The data model should consist of a separate table with a unique identifier and the various attributes in question. But for argument sake let us imagine that this is not available and that we need to generate a temporary table consisting of these attribute values.

mysql> create table data (id int auto_increment primary key, attributes varchar(255));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into data(attributes) values ('One,Two,Three,Four'),('a,b,c,d,e'),('Dog,Cat,Horse');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from data;
+----+--------------------+
| id | attributes         |
+----+--------------------+
|  1 | One,Two,Three,Four |
|  2 | a,b,c,d,e          |
|  3 | Dog,Cat,Horse      |
+----+--------------------+
3 rows in set (0.00 sec)

Let us now create a stored procedure which takes each of these records and adds them to a temporary table that we have created.

drop procedure if exists sp_data;
delimiter $$
create procedure sp_data()
begin
	declare done int default 0;
	declare lattributes varchar(255);
	declare lid integer;
	declare idx integer;
	declare cdata cursor for select id, attributes from data;
	declare continue handler for not found set done := 1;
	create temporary table if not exists tdata(id int, attribute varchar(100));
	open cdata;
	read_loop: loop
		fetch cdata into lid, lattributes;
		if done then
			leave read_loop;
		end if;
		attr_loop: loop
			set idx := locate(',', lattributes);
			if idx > 0 then
				set @lattribute := substring(lattributes, 1, idx-1);
			else
				set @lattribute := lattributes;
			end if;
			INSERT INTO tdata(id, attribute) VALUES (lid, @lattribute);
			if idx = 0 then
				leave attr_loop;
			end if;
			set lattributes := trim(substring(lattributes, idx+1));
		end loop;
	end loop;
end;
$$
delimiter ;

As we have a temporary table the results are only visible for the duration of the session. Afterwhich these are removed by the MySQL server.

Calling this function on this table we get:

mysql> select * from tdata;
ERROR 1146 (42S02): Table 'test.tdata' doesn't exist
mysql> call sp_data;
Query OK, 1 row affected (0.00 sec)

mysql> select * from data;
+----+--------------------+
| id | attributes         |
+----+--------------------+
|  1 | One,Two,Three,Four |
|  2 | a,b,c,d,e          |
|  3 | Dog,Cat,Horse      |
+----+--------------------+
3 rows in set (0.00 sec)

mysql> select * from tdata;
+------+-----------+
| id   | attribute |
+------+-----------+
|    1 | One       |
|    1 | Two       |
|    1 | Three     |
|    1 | Four      |
|    2 | a         |
|    2 | b         |
|    2 | c         |
|    2 | d         |
|    2 | e         |
|    3 | Dog       |
|    3 | Cat       |
|    3 | Horse     |
+------+-----------+
12 rows in set (0.00 sec)