Cybersecurity blog header

MySQL stored procedures protection

Welcome to the new Tarlogic blog, where we will try to unroll technical articles focused on website security audits or cloud applications security and services deployment.

In this article we will talk about the protection of sql injection attacks on MySQL database stored procedures.

Sometimes, part of the data access operation of applications is performed through the use of MySQL stored procedures.

The need for the use of stored procedures in MySQL is very varied and may depend largely on the type of application being developed but, most people agree that three of the main reasons for its use are:

  1. Integrity of the information: Centralizing the access to certain information through a single mechanism.
  2. Security, because of the possibility offered by a stored procedure to execute actions with different privileges.
  3. Performance, since it is the engine itself that performs the transactions.

The main way to ensure the security of code executed within a stored procedure is by using a prepared statement in which the procedure parameters are properly validated, so as to avoid SQL injection attacks. Below is an example of a prepared query to which two parameters are sent:

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;

When the SQL code executed within a procedure is dynamic SQL code, generated based on parameters sent by the user or, in certain SQL statements, which do not support the use of prepared statements, it can be complex to ensure safety in the execution of the SQL code.

It has been previously indicated that a stored procedure can be executed with higher privileges. Therefore, the risk to database security posed by executing a poorly programmed stored procedure is high. A database stored procedure is a potential target for hacker attacks.

The reader is probably thinking that in order to validate the data, it is the web application engine itself that must filter the data. This is only partly correct.

The application must be able to protect the database from malformed entries but, if a user is able to connect directly to the database, for example from one of our compromised web servers, this makes it easier for an intruder to execute arbitrary commands with the privileges of our stored procedure.

This attack is not trivial, but it is a very interesting way to compromise a system.

The alternative to this dilemma is simple to explain. It should be up to the stored procedure to verify the type of data it expects and to remove from the input parameters those SQL control characters that may modify the logical flow of the query.

Unfortunately, MySQL does not offer any native call to validate input data as is the case with PHP’s mysql_escape_string. To work around this, you can develop your own validation procedure.

-- -----------------------------------------------------
-- function my_mysql_escape_string
-- -----------------------------------------------------
DROP FUNCTION IF EXISTS `my_mysql_escape_string`;
SHOW WARNINGS;DELIMITER $$
CREATE FUNCTION my_mysql_escape_string( cadena VARCHAR(255) ) RETURNS VARCHAR(255)
BEGINRETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(cadena,'\\','\\\\'),'\0','\\0'),'\n','\\n'),'\r','\\r'),'\x1a','\\z'),'"','\\"'),"'","\\'");
RETURN @cadena;
END

From this point on, you can start sending the input parameters without filtering since the stored procedure itself is in charge of avoiding SQL injection attacks.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `protected_against_sql_injection`(IN UserName VARCHAR(50))
SET @_UserName = my_mysql_escape_string(UserName);
-- do stuff
-- do more stuff
END
$$
DELIMITER ;

With this simple MySQL function we can protect stored procedures that generate dynamic SQL code in a fast and safe way.

Greetings and see you next time.

Discover our work and cybersecurity services at www.tarlogic.com