Open Source Software Technical Articles

Want the Best of the Wazi Blogs Delivered Directly to your Inbox?

Subscribe to Wazi by Email

Your email:

Connect with Us!

Current Articles | RSS Feed RSS Feed

Improve security with MySQL stored procedures

  
  
  

Stored procedures, which are available in all popular SQL engines, including MySQL, allow you to save SQL statements with variable declarations and flow control for later use. In MySQL, stored procedures have been supported since version 5.0, which means they are available in the default packages for CentOS and all other recent Linux distributions. You may have heard of stored procedures before, but do you know that you can use them to enhance your security?

When you create a stored procedure to retrieve data, the first thing to do is to change the default MySQL delimiter – the semicolon (;). This is because in your procedure you will use at least one MySQL query with the default delimiter, and if the default delimiter remains unchanged you will end the declaration of the procedure prematurely.

When you specify a different delimiter – such as // – you can safely complete the declaration of the procedure without interrupting it at the end of a query. To set a different delimiter, use the option DELIMITER followed by the desired delimiter as an argument, as in DELIMITER //. Once you finish declaring the procedure you should restore back the default delimiter with the command DELIMITER ;.

Next, choose a name for your procedure and declare it with the statement CREATE PROCEDURE procedure_name([parameters]) [options]. Both the parameters and the extra options have security implications.

You can define three types of parameters: IN, OUT, and INOUT. IN parameters send input to the procedure; by default all parameters are input. OUT parameters receive output from the procedure. INOUT parameters are used for both input and output.

You can also define parameters using the usual MySQL data types, and by doing so, you can enhance security. For example, if you expect the input parameter to be an integer, define it as such so an attacker cannot pass a different type of value to your procedure.

Consider the following example:

DELIMITER // 
CREATE PROCEDURE int_check(IN number INT) 
BEGIN 
SELECT number;
END // 
DELIMITER ; 

This procedure works properly if you invoke it with an integer parameter such as the number 7:

mysql> CALL int_check(7);
+--------+
| number |
+--------+
|      7 |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

However, it returns an error if you try to pass a string:

mysql> CALL int_check('test');
ERROR 1366 (HY000): Incorrect integer value: 'test' for column 'number' at row 1

This is obviously a good security measure, but even when you define the data type of the parameters, you do not automatically prevent SQL injections. If an attacker is able to pass special characters to a parameter of the procedure, he may still be able to compromise your security. Suppose an attacker passed as an argument to this procedure the value 7;show tables;. The result would be that the corresponding MySQL database tables would be shown, thus compromising your security. This means your application still has to sanitize the parameters it passes. MySQL stored procedures add a level of security but they do not replace the need for application security.

As we saw with input parameters, you can define data types for output (OUT) and input/output (INOUT) parameters as well to limit the type of data you retrieve. Here's an example with an output parameter combined with the code above:

DELIMITER // 
CREATE PROCEDURE out_check(IN long_text VARCHAR(10), OUT short_text VARCHAR(5)) 
BEGIN 
SELECT long_text INTO short_text;
END // 
DELIMITER ; 

In the above example you can specify input text containing up to 10 characters. When the procedure is executed, the output parameter, @short_text, can contain only five characters of text. If the output exceeds this limit, the procedure call fails. This security measure can limit the information that may be disclosed. Here is an example with an unsuccessful call:

mysql> CALL out_check('long text',@short_test);
ERROR 1265 (01000): Data truncated for column 'short_text' at row 1
mysql> SELECT @short_test;
+-------------+
| @short_test |
+-------------+
| NULL        |
+-------------+
1 row in set (0.00 sec)

Here is a successful example:

mysql> CALL out_check('short',@short_test);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @short_test;
+-------------+
| @short_test |
+-------------+
| short        |
+-------------+
1 row in set (0.00 sec)

Stored procedures are not only about security, of course. In the above example, the output from the procedure is used to set a user-defined variable: @short_test. Implementing variables and other programming logic in MySQL allows you to transfer some of the programming burden to the database layer.

Implementing user privileges for better security

MySQL procedures have a different set of privileges from the usual database and table permissions. Thus, even if a user does not have read rights for a table, the user may still be able to retrieve table information through a procedure.

You most commonly use execute permission when working with MySQL procedures. This permission allows a user to run the procedure without being able to modify it. You can grant this permission with options such as GRANT EXECUTE ON PROCEDURE employees.GetEmpoyeeName TO test1@'localhost';. This statement grants execute privileges on the employees sample database for the user test1 from localhost.

The example procedure GetEmpoyeeName retrieves the names of an employee according to his employee number(emp_number column). The code of the procedure itself is:

CREATE PROCEDURE `GetEmpoyeeName`(IN enumber INT(5))
BEGIN
SELECT first_name, last_name
FROM employees
WHERE emp_no =  enumber;
END

If you run the procedure as the user test1, you will get the needed information. For example:

mysql> CALL GetEmpoyeeName (10004);
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Chirstian  | Koblick   |
+------------+-----------+

However, if you were to try to get exactly the same information directly, without calling the procedure, and lacked read access to the table, you would not be able to do so:

mysql> SELECT first_name, last_name FROM employees WHERE emp_no =  10004;
ERROR 1142 (42000): SELECT command denied to user 'test1'@'localhost' for table 'employees'

MySQL procedures also have an option called SQL SECURITY that determines in what context and with what privileges a procedure is executed. The two SQL SECURITY options are definer and invoker. By default, a procedure is executed in the definer context, meaning the context of the user who created the procedure. It is because of this that we could run the procedure above and get the results we wanted; the procedure was created by the privileged root user. If the SQL SECURITY option were set to invoker, the test1 user would not be able to execute the procedure successfully. Here is an example:

CREATE PROCEDURE `GetEmpoyeeName2`(IN enumber INT(5))
SQL SECURITY INVOKER
BEGIN
SELECT first_name, last_name
FROM employees
WHERE emp_no =  enumber;
END

If the test1 user still does not have read privileges to the employees table, a call to the procedure will result in a failure:

mysql> CALL GetEmpoyeeName2 (10004);
ERROR 1142 (42000): SELECT command denied to user 'test1'@'localhost' for table 'employees'

Keeping the default definer option for SQL SECURITY lets you create procedures in the context of a user that has read access to the tables and invoke them from another user that lacks such access. This isolation and separation is a good way to decrease security risks and further protect your information.

See how useful MySQL stored procedures can be for data security? Now you should understand why banks and government agencies use stored procedures to work with sensitive data.




This work is licensed under a Creative Commons Attribution 3.0 Unported License
Creative Commons License.

Comments

Many internet websites include unique levels of looking for unique member's program forms, like these intended for firms and as well these intended for frequent specific shoppers. Minimum amount prerequisites may differ just a web page intended for firms exactly who prada outlet with him or her in addition to people that retail outlet with him or her, and so know that people know precisely what is expected of this acquire. Something diffrent it really is a big motivator to help looking with most of these internet websites is usually of which together with below wholesale clutches quite a few in addition have many unique gadgets into their replica coco handbags far too. Persons can buy anything and everything by glasses, pouches, coin totes, shoes, jewelry, bracelets and even more that they may acquire along with their carriers. Persons typically can grant corporations the mail deals with far too to obtain super deals, discount coupons, in addition to exclusive notifies in relation to gross sales prior to a usual open replica chanel coco. You will discover replica chanel along with promo ebooks of which please don't possibly previously receive posted out to you towards frequent open which might be only reserved for these exclusive mail customers. To be sure that you will get below wholesale clutches for the very best charges you'll want to search a tad prior to produce almost any expenses. For anyone who is some sort of financial institution connected with antiques, you will discover many traditional chanel replica on the net which can be connected with fascination to your account. There are various people that choose to obtain older retro objects. A lot of the lumber objects will probably indicate this worth that had been through with fortitude in addition to health care. A number of people obtain them simply because may perhaps develop within a state which often supports meaning towards fake rolex watches. Intending collected from one of retail store completely to another in search of a selected fake rolex are often very difficult. Numerous merchants include factors loaded in concert which help the item tricky to take into consideration the product that you are hoping; many outlets often have a small number of merchandise out there. Obtaining antiques on the net is usually a easy in addition to effortless strategy to retail outlet from the ease of ones own property. There'll be many fake chanel that can include retro merchandise. It will be easy to help look through by retail outlet to go without difficulty. Many outlets should have the merchandise sorted for making the item simple to operate all around on their web page. You'll discover many types connected with objects like figures constructed from everywhere over the earth, retro clutches, retro apparel, unique board games on the beyond, a range of games that can reestablish remembrances to a lot seniors and much chanel 2.55 bags
Posted @ Tuesday, May 13, 2014 3:43 AM by replica chanel leboy
Good!This post is creative,you'll find a lot of new idea,it gives me inspiration.I believe I will also inspired by you and feel about extra new ideas.thanks.
Posted @ Friday, May 16, 2014 10:24 AM by Campmor Coupon
Post Comment
Name
 *
Email
 *
Website (optional)
Comment
 *

Allowed tags: <a> link, <b> bold, <i> italics