Dangers of Adding fields to Database Tables

There are certain dangers if you don’t plan carefully before adding fields to your database. Though, it seems obvious but its not as simple as you might think. If you don’t plan properly you may miss something or ruin the current table which will consume lot of your time fixing. You must have come to a situation where things are looking so obvious but you aren’t getting a proper result. The cause behind the unexpected result can be careless addition to database fields. Lets check few scenarios where these things apply.


A SELECT * statement returns all field values from a table. While this is not only a lazy practice, returning more fields than needed will lead to performance problems in your application. In addition to potential performance problems, SELECT * statements can cause your application code to break when additional fields are added to your database table. Consider the following example. Database table Sales has four fields; SaleID, SalePrice, SaleDate and Product. A front-end application calls the following stored procedure to acquire sales for the last two days.

When this procedure is run, it returns all four fields from the Sales table. If the front-end application is coded in such a way so that it tries to handle only these four fields, returning an additional field may result in a broken front end. While something like this should be caught through testing, little situations like this may end up leaking through to production code which would result in a “broken” application.

The most simple method to correct this situation is to not use a SELECT * statement. If you always explicitly list the field names that you want your procedures to return then you’re much more likely to avert these types of problems as well as enjoy some performance gains from not returning all fields in the SELECT statement.


Adding database fields to tables that participate in JOIN statements (probably most of your tables) can potentially cause problems. These problems can arise when the name of the field added also exists as a field name in the joined table and table aliases are not used.

To make this a bit clearer, consider the following example. The Products table has three fields; ProductID, ProductCode and ProductDescription. The Sales table has the following fields; SaleID, ProductID, SaleDate. Most queries join these tables in the form of:

Because most queries are only concerned with the ProductCode from the Products table, a developer as decided to add the ProductCode field to the Sales table to avoid the look-up. Adding the ProductCode to the Sales table will not only go against normalization rules (which is sometimes OK), but will immediately cause the query listed above to fail.

The reason is that no table aliases are used when listing the fields, so the optimizer will not know whether the query is specifying the ProductCode from the Products table or the Sales table. To fix this problem, any statement that looks at the ProductCode from the JOIN of these two tables and which does not use table aliases must be rewritten. To avoid this problem entirely, ALWAYS use table aliases when joining two or more tables.


Adding a field to a table should not cause your programs to crash. The problems encountered here could be very easily avoided if you include these types of situations in your database programming standards and ensure they are enforced. A little bit of looking forward can prevent many hours of reprogramming.

If you’ve got any thoughts, comments or suggestions for things we could add, leave a comment! Also please Subscribe to our RSS for latest tips, tricks and examples on cutting edge stuff.

MySQL: foreign keys and referential integrity

Recent versions of MySQL have implemented support for foreign keys through the InnoDB table engine. Following is an explaination of how it works.

Referential integrity is an important concept in database design. The term refers to a state when all the references in a database are valid and no invalid links exist between the various tables that make up the system. When referential integrity exists, any attempt to link to a record which does not already exist will fail; this helps prevent user errors, producing a more accurate (and useful) database.

Referential integrity is usually implemented through the use of foreign keys. For a long time, the popular open-source RDBMS MySQL did not support foreign keys, citing concerns that such support would erode RDBMS speed and performance. However, given the high volume of user interest in this feature, later versions of MySQL implemented support for foreign keys through the InnoDB table engine. Consequently, maintaining referential integrity within the tables that make up a database is significantly simpler.

In order to set up a foreign key relationship between two MySQL tables, three conditions must be met:

  1. Both tables must be of the InnoDB table type.
  2. The fields used in the foreign key relationship must be indexed.
  3. The fields used in the foreign key relationship must be similar in data type.

The best way to understand how this works is with an example. Begin by creating two tables, one listing animal species and their corresponding codes (table name: species) and the other listing animals in a zoo (table name: zoo). The idea here is to link the two tables by the species code, so that only those entries in the zoo table which have a valid species code in the species table are accepted and saved to the database.

Important: For non-InnoDB tables, the FOREIGN KEY clause is ignored.

As the above illustrates, a foreign key relationship now exists between the fieldszoo.species and species.id. An entry in the zoo table will be permitted only if the corresponding zoo.species field matches a value in the species.idfield. This is clearly visible in the following output, which demonstrates what happens when you attempt to enter a record for Harry Hippopotamus with an invalid species code:

Here, MySQL checks the speciestable to see if the species code exists and, finding that it does not, rejects the record. Contrast this with what happens when you enter the same record with a valid species code (one that already exists in the species table):

Here, MySQL checks the species table to see if the species code exists and, finding that it does, permits the record to be saved to the zoo table.

To delete a foreign key relationship, first use the SHOW CREATE TABLE command to find out InnoDB’s internal label for the field.

And then use the ALTER TABLE command with the DROP FOREIGN KEY clause, as below:

To add a foreign key to an existing table, use the ALTER TABLE command with an ADD FOREIGN KEY clause to define the appropriate field as a foreign key:

As the examples above illustrate, foreign key relationships can play an important role in catching data entry errors, and implementing them usually results in a stronger, better-integrated database. On the other hand, it’s worthwhile noting that performing foreign key checks is a resource-intensive process and defining complicated inter-relationships between tables can result in a significant performance drop. Therefore, it’s important to always balance referential integrity considerations with performance considerations, and use foreign keys judiciously to ensure an optimal mix of speed and strength. 

Please post your valuable comments if you like to share your experiences with us. If you like this post kindly subscribe to our RSS for free updates and articles delivered to you.

Secure sensitive data in MySQL with few easy steps

Many hackers constantly try to breach your secure data and steal your secure information away. People generally think that they can’t secure their data actively but this isn’t true. If you’re using MySQL, there are some easy things you can do to secure your systems and significantly reduce the risk of unauthorised access to your sensitive data.

The most valuable asset for technology-based organisations is usually the customer or product information in their databases. And so, a critical part of database administration in such organisations consists of securing these databases against outside attack and hardware/software failures.

In most cases, hardware and software failures are handled through a data backup regimen. Most databases come with built-in tools to automate the entire process, making this aspect of the job relatively painless and error-free. What’s not so simple, however, is the second half of the puzzle: making sure that outside hackers can’t get into the system and either steal or damage the information contained therein. And unfortunately, there usually isn’t an automated way to solve this problem; rather, it requires you, the administrator, to manually put in place roadblocks and obstacles to trip up would-be hackers and to ensure that your company’s data stays secure.

A common reason for not securing databases is that it is “difficult” and “complicated”. While this is certainly true, if you’re using MySQL, there are some easy things you can do to significantly reduce the risk you face. This tutorial lists six such items, but you can find many more in the MySQL manual and discussion forums.

Step 1: Remove wildcards in the grant tables

The MySQL access control system works through a series of so-called grant tables, which make it possible to define access levels for each user at the database, table or column level. While these tables do allow administrators to set blanket permissions for a user or set of tables using wildcards, doing so is inherently dangerous because a hacker could use a single compromised account to gain access to other parts of the system. For this reason, be precise when assigning users privileges and always ensure that users have only as much access as they need to perform their tasks. In particular, be wary of assigning the SUPER privilege to individual users, as this level allows users to manipulate basic server configuration and access all databases.Tip: Use the SHOW PRIVILEGES command for each user account to audit your grant tables and see if the use of wildcard permissions is appropriate.

Step 2: Require the use of secure passwords

User accounts are only as secure as the passwords used to protect them. For this reason, the very first thing you should do when you install MySQL is assign a password to the MySQL root account (empty by default). Once you’ve closed this gaping hole, the next step is to require that every user account must have a password and ensure that passwords do not use easily-recognisable heuristics such as birthdays, user names or dictionary words.Tip: Use the MySQL –secure-auth option to prevent the use of older, less secure MySQL password formats.

Step 3: Check the permissions of configuration files

Very often, to make server connections faster and more convenient, both individual users and server administrators store their user account passwords in their per-user MySQL options file. However, this password is stored in plain-text within the file and can easily be read. Therefore, it’s important to ensure that such per-user configuration files are not viewable by other users of the system, and are stored in non-public locations. Ideally, you’d want the per-user configuration to be stored in the user’s home directory with permissions 0600. 

Step 4: Encrypt client-server transmissions

An important issue in the MySQL (and any) client-server architecture involves the security of data being transmitted over the network. If client-server transactions take place in plaintext, it is possible for a hacker to “sniff” the data packets being transmitted and thus gain access to sensitive information. You can close this hole either by enabling SSL in your MySQL configuration, or by using a secure shell utility like OpenSSH to create a secure encrypted “tunnel” for your data to pass through. Encrypting your client-server connection in this manner makes it extremely hard for unauthorised users to read the data going back and forth. 

Step 5: Disable remote access

If your users don’t need to access the server remotely, you can significantly reduce the risk of a network attack by forcing all MySQL connections to take place via the UNIX socket file. This is accomplished by starting the server with the –skip-networking option. Doing this blocks TCP/IP network connections to MySQL and ensures that no user can remotely connect to the system.

Tip: An enhancement to this would be to add the directive bind-address= in your MySQL server configuration, to force MySQL to bind to the IP address of the local machine and thus ensure that only users on the same system can connect to MySQL.

Step 6: Actively monitor the MySQL access log

MySQL comes with a number of different log files, which keep track of client connections, queries and server errors. Of these, the most important is the general query log, which logs each client connection and disconnection with a timestamp, and also records each query executed by a client. If you suspect unusual activity, such as that associated with a network break-in attempt, it’s a good idea to monitor this log to gain an understanding of the source of the activity. 

Protecting your MySQL databases is an ongoing task, and so you shouldn’t rest easy once you’ve done the steps above. Visit the MySQL manual and the MySQL forums for more security tips, and be proactive in monitoring and updating the security of your system.

Please post your valuable comments if you like to share your experiences with us. If you like this post kindly subscribe to our RSS for free updates and articles delivered to you.

Setting-Up a Relational Database in MySQL

Relational Database Design is one of the most powerful ways to ensure data integrity and a great way to kick-off any project. Very often the first thing developers do when starting a new project, or stub-project, is to design the database. This way the structure of the application is already in place and we just have to fill in the pieces with some server-side code. I’ve found when adding relational constraints to your database design you add in a very powerful error reporting tool that will let you know during the development process that you have allowed something to happen that shouldn’t have. In this article, I go through, step by step, showing how to set up a simple relational database and discuss the benefits that are enjoyed.

Let’s take a step back and describe what a relational database looks like. In any normal database design there are fields in one table that reference another table. For example, a books table might have a field labeled author_id which is meant to come from a table named authors. Creating hard-coded relations solidifies these associations and actually returns a MySQL error if violated.

As I hinted in the opening I have found this to be invaluable during the development and testing process as MySQL will immediately let me know that I have made a glaring error that otherwise may not have been noticed until after the service has launched. At that point the data could be irreparably corrupt and forced to start from scratch.

So let’s get right to it. For the purposes of this article, I’m going to pretend I’m creating a simple Books and Authors website with a simple 2-table setup. The first step is to create our tables.

  1. CREATE TABLE `library`.`books` (
  3. `name` VARCHAR( 150 ) NOT NULL ,
  4. `author_id` INT UNSIGNED NOT NULL ,
  5. PRIMARY KEY ( `id` ) ,
  6. INDEX ( `author_id` )
  7. ) ENGINE = InnoDB

  1. CREATE TABLE `authors` (
  2. `id` int(10) unsigned NOT NULL auto_increment,
  3. `name` varchar(50) collate utf8_bin NOT NULL,
  4. PRIMARY KEY  (`id`)

Relational Database - 1

Nothing too fancy here. Couple things to notice:

  1. Each table MUST be using the InnoDB storage engine. InnoDB is currently the only main-stream storage engine offered by MySQL to support relational design. More on this in my article: MyISAM vs InnoDB
  2. The `author_id` field in the `books` table MUST be indexed and the same datatype as the `id` field in `authors`.

The next step is to set up the relations. Open the `authors` table and take a look at the view. Under the table there should be a link titled ‘Relation View’ – Click it.

Relational Database - 2

phpMyAdmin has a great gui for setting up relations and actions. If the `author_id` row below doesn’t look like mine, make sure you have it indexed.

Relational Database - 3

Here, I’ve setup a link on the `books` table and the `author_id` field. This will enforce the fact that any value inserted in this field MUST be present in the `authors.id` table as well. But what about these other settings?


    • This means if an author is deleted from the authors table, all of his books will also be automatically deleted.
    • This option is great to keep your data clean and reduce the number of delete quieries required when deleting an author.
    • Instead of deleting the book record when an author is deleted, books.author_id is set to NULL, effectively orphaning the book.
    • This feature is great if you want to be able to keep the books and come back at a later time to reassign them. Otherwise, without this feature, the books would still be referencing an author_id that doesn’t exist.
    • Note: If you try to set this option and phpMyAdmin tells you to check your datatypes, make sure the field is allowing null values.
    • When a delete query is issued on an author that has books, MySQL will not allow this and return a Foreign_Key Constraint error.
    • It could be nice to identify this and re-word it to let the user know that if they would like to delete this author they need to re-assign his books or delete them all-together.
    • Note: If you use this option please remember to re-word the MySQL error to something the user can easily understand.
    • Same as NO ACTION
    • From MySQL Manual: Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION and RESTRICT are the same.


  • For the most part the options described above are going to act in the same manner they did for ON DELETE as they will with ON UPDATE. I’ll just run through some examples real quick.
    • If, for some reason, an author’s id gets updated than CASCADE will update all his corresponding books with the new value. Extremely handy.
    • Same as CASCADE except instead of updating it with the new value, it will set it to null. I’m sure there is a perfectly good use for this but I haven’t run into it yet. If anyone can enlighten me please do 🙂
    • Same as ON DELETE, will throw an error if you try to update an author_id. I’m also having trouble finding a real-world example of when this could be useful

Once we have our simple relational database configured try to add a book with an author_id that doesn’t exist. MySQL should give you an error like this:

Cannot add or update a child row: a foreign key constraint fails (`library/books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

PHP: Securing Your Input Forms From MySQL Injection Attacks

Every website has ‘em. Forms. Places for users to enter data into your website. Whether it be a search box, a “Contact Us” form, or variables in the website address, at some point in the flow of your script these suckers are going to touch your database. Oh, that’s no problem — We’ll just take what they type in and run a query in MySQL on it!

WHOA, there! Are you sure you want to do that? Any input from a user should be treated like a nuclear fuel rod. You can handle it, but you’ve got to make sure you do it right. You wouldn’t just pick it up with your bare hands, would you?

Why? Just what are MySQL Injection attacks anyway?

Lets say your database has a table inside called ‘tbl_Users’. Inside ‘tbl_Users’ are a list of your users, which all have usernames, passwords, first names, last names, addresses, etc. If these users are presented with a login box somewhere on your site, your php user verification query might be something like this:

SELECT * FROM `tbl_Users` WHERE `username`='”.$_POST[‘username’].”‘ AND `password`=’”.md5($_POST[‘password’]).”‘”The problem is that unscrupulous users (read: bad ones) could enter this into your form:


username: no_onepassword: ‘ OR ”=”Which would make your query look something like this:

SELECT * FROM `tbl_Users` WHERE `username`=’no_one’ AND `password`=” OR ”=”Which, if you read that correctly, would allow that user access to whatever it was you wanted hidden by logging them in. There are a multitude of other ways this can be dangerous, but this is by far the easiest example. Even more unscrupulous users (read: the real jackasses) could send in multiple queries including DELETE queries.

In which case, when you wake up the morning after the attack you are most likely to be heard saying:”Hey, where did all my users go?” 

Wow. Okay so I’ve got a friend… and his website isn’t secure. What can I do to help him out?

The good news is that with a few easy precautions, your “friend’s” website will be pretty secure against these types of attacks. I say pretty secure because there is no way to prevent every attack. We can only do our best to increase security to a point to take every realistic precaution to prevent these attacks.

#1: Escape your variables!

Using the php function ‘mysql_real_escape_string’ you can “escape” the single quote character from user input. This is probably the easiest method to prevent MySQL injection attacks. It works by adding a backslash (”\”) before each quote that the user enters into their input. So, to use our example from before:

username: hey’therebecomes

username: hey\’thereThis effectively stops MySQL injection in its tracks since it not only escapes the single quote (”‘”) character but also all other characters that the baddies can use to hijack your queries.

If you’ve got an array of data coming in, you can use this neat function that I found on the PHP mysql_real_escape_string page (code by “brian dot folts at gmail dot com”). It escapes all of the values in your array with ease.

To escape an array, use this function:

function mysql_real_escape_array($t){
return array_map(”mysql_real_escape_string”,$t);

Then you can call that function easily by passing your array to it:

$your_array = mysql_real_escape_array($your_array);

#2: Check the variable type of your input.

This is done by using the php functions “is_numeric()“, “is_string()“, “is_float()“, and “is_int()” to determine if the input the user is sending in is the same type that you were asking for. It’s not perfect, but if you were asking for a number and they sent in a word you know to discard it straight away and return an error thereby entirely avoiding any change of a MySQL injection attack.

#3: Always use proper MySQL syntax, including “`” and “‘” characters.

If your queries look something like this:

SELECT * FROM tbl_Users WHERE username=$value; Rewrite it so that it looks more like this:

$value = mysql_real_escape_string($value);mysql_query(SELECT * FROM `tbl_Users` WHERE `username`='”.$value.”‘”); Proper MySQL syntax requires that all table and field names are surrounded by the reverse apostraphe (”`”) and values surrounded with single quotes / apostraphe (”‘”).

I hope this gives you a better indication of what you can do to help secure your websites. Keep in mind that this is in no way a complete list. Be ever vigilant in your efforts to prevent attacks of any kind on your code. Leave a comment or two if this helped you at all or if you have different suggestions on how to secure your code from injection attacks!

Database Normalization and Table structures

Normalisation is the term used to describe how you break a file down into tables to create a database. There are 3 or 4 major steps involved known as 1NF (First Normal Form), 2NF (Second Normal Form), 3NF (Third Normal Form) and BCNF (Boyce-Codd Normal Form). There are others but they are rarely if ever used. A database is said to be Normalised if it is in 3NF (or ideally in BCNF). These steps are descibed as follows:

Note: When attribute is used we are speaking of a field in the table

To put a database in 1N

  • ensure that all attributes (columns) are atomic (which means that any single field should only have a value for ONE thing).


In a database a table on Customers would have an address attribute. The address is made up of Company Name, Address Line1, Address Line2, Address Line3, City, Postcode. There are 6 values to this address and as such each should have it’s own field (column).

If your company sold furniture a table on products could have a description attribute. If for example that attribute was ‘Beech Desk 120w x 75h x 50d’. Ideally this would be broken down into a number attributes like ‘Colour’, ‘Type’, ‘Width’, ‘Height’ and ‘Depth’. The reason for this is it would allow you to seach the database for all Desks, for all pieces of Beech furniture, for all desks with a width of 120 etc.

  • Create a separate table for each set of related data and Identify each set of related data with a primary key


In a general Invoicing table you would have a separate table for Customers, Orders, Products, Invoices and you would probably need tables for OrderDetails and InvoiceDetails as well. Each of these tables must have their own primary key. Each of these tables except for customers would have a foreign key reference to the primary key of another table. (See Relationships below)

  • Do not use multiple fields in a single table to store similar data

(Underlined fields are Primary Keys and Italicised fields are Foreign Keys)

In a customer order you could have more than one product. That is the customer has ordered more than one item. If you tried to put all of this in one table as {OrderID, CustomerID, OrderDate, Product1, Product2, Product3} what would happen if the customer ordered more than 3 products. There would also be implications for querying the kind or quantiy of products ordered by a customer. Therefore these product fields don’t belong in the order table which is why we would have an OrderDetails table which would have a foreign key refernce to the Orders table {OrderDetailsID, OrderID, ProductID, Quantity}. Using productID as a foreign key to the product table means you don’t have to identify the product attributes here. This also allows you to enter a quantity figure for the product ordered.


All tables should have a 1 to 1 or 1 to many relationship. This means for example that 1 customer can have 1 or many orders and 1 order can have 1 or many details.

Therefore Orders table would have a foreign key reference to the Customer table primary key {OrderID, CustomerID, OrderDate} and the OrderDetails table would have a foreign key reference to the Order table primary key {OrderDetailsID, OrderID, ProductID, Quantity}. This table also contains a foreign key reference to the Products table. As a product is likely to be ordered more than once there is a many to 1 relationship between the OrderDetails and the Products table.

If any tables have a many to many relationship this must be broken out using a JOIN table. For example, Customers can have many Suppliers and Suppliers can supply to many Customers. This is known as a many to many relationship. You would need to create a JOIN table that would have a primary key made up of a foreign key reference to the Customers table and a foreign key reference to the suppliers table. Therefore the SuppliersPerCustomer table would be {SupplierID,CustomerID}. Now the Suppliers table will have a 1 to many relationship with the SuppliersPerCustomer table and the Customers table will also have a 1 to many relationship with the SuppliersPerCustomer table.


The database must meet all the requirements of the 1NF.

In addition, records should not depend on anything other than a table’s primary key (a primary key can be made up of more than one field, only if absolutely necessary like in a JOIN table).


A customers address is needed by the Customers table, but also by the Orders, and Invoices tables. Instead of storing the customer’s address as a separate entry in each of these tables, store it in one place, either in the Customers table or in a separate Addresses table.


The database must meet all the requirements of the 1NF and 2NF.

The third normal form requires that all columns in a relational table are dependent only upon the primary key. A more formal definition is:

  • A relational table is in third normal form (3NF) if it is already in 2NF and every non-key column is non transitively dependent upon its primary key.

In other words, all nonkey attributes are functionally dependent only upon the primary key. All 3NF really means is that all fields (attributes) should be dependent on the tables primary key. If they are not they should be put in their own table. This means that every attribute unless it is a primary or foreign key must be DIRECTLY dependent on the Primary Key of this table and not on some other column.


The Customer table contains information such as address, city, postcode imagine it also contained a column called shipping cost. The value of shipping cost changes in relation to which city the products are being delivered to, and therefore is not directly dependent on the customer even though the cost might not change per customer, but it is dependent on the city that the customer is in. Therefore we would need to create another separate table to hold the information about cities and shipping costs.


A relation is in Boyce-Codd Normal Form (BCNF) if every determinant is a candidate key. BCNF is very similar to 3NF but deals with dependencies within the primary keys. BCNF in it’s simplist terms just says don’t have a primary key made up of more than one field unless it is a join table to disperse a many to many relationship and only contains the two primary keys of the tables it is joining.

Most relations that are in 3NF are also in BCNF. It only happens that a relation which is in 3NF is not in BCNF when the primary key in a table is made up of more than one field and the other columns are not dependent on both fields but only on one or the other.