Mysql

SANS GCIA Gold Paper (OHIDS)

Posted on

My SANS GCIA gold paper has been published! It was a lot of work, but I’m very excited about it.  You can get the paper here and the software here.

Abstract

Responding to incidents in an efficient manner is critical for all CIRTS. This paper presents a new open
source tool for the enterprise. With this tool, responders will be able to detect incidents using aggregated
data collected from hosts and applying anomaly detection. OHIDS includes a sensitive data finder to allow
appropriate escalation of the incident. This software can be utilized in a proactive manner by removing SSNs
and credit card data before incidents occur or by detecting unauthorized software running.

 

Advertisements

MySQL Stored Procedure, Prepared Statements and SQL Injection

Posted on

I’ve been working on a MySQL project lately. This application does not take input directly from the user, but it still has to query the database to setup variables to then input data.

I wanted to be sure that I was using the best technique for preventing SQL injection. Originally I was planning to use a stored procedure and escape the input parameter, but I found out about prepared statements. It works by setting up the SQL statement and setting a place holder with a ‘?’ for input. What this does is tells MySQL to read this as data and prevents attackers from changing the query results.

'Select * from table where name is ?'

You can have your VB.net, PHP or other languages build the prepared statement for you, but I wanted to build the logic on the server side. This give the client access to execute the stores procedure and not allow direct permissions to the tables.

The basic syntax for a prepared statement is you prepare the statement with a name, give the SQL query to preform, execute the statement and then deallocate the statement.

prepare id from
 'select Id from table where name= ?' ;
 set @myname :='mycomputer';
 execute id @myname;
 DEALLOCATE PREPARE id;

The sql statement above does the following:

  1. Sets the name of the statement to “id”
  2. Sets the SQL statement and has a place holder for the name in the query
  3. Sets the variable @myname to “mycomputer”
  4. Executes the statement “id” using the variable @myname as the query value ‘?’
  5. Removes the prepared statement

Using the information above. The statement that is run is

'select Id from table where name= mycomputer' ;

Now I want to take the prepared statement and make it a stored procedure.

 DROP PROCEDURE IF EXISTS `table`.`get_id`;
 DELIMITER $$
 CREATE PROCEDURE `IR`.`get_id`(IN input VARCHAR(20), OUT id VARCHAR(5))
 READS SQL DATA
 BEGIN
 SET @input=input;
 prepare id from
 'select Id from table where name= ?' ;
 execute id USING @input;
 DEALLOCATE PREPARE id;
 END$$
  1. If the procedure already exists, delete it
  2. Setup the delimiter for starting and stoping the procedure text
  3. Creates the procedure name get_id in the table. It allows input of up to 20 characters and allows output to be up to 5 characters. By limiting the input and output, if injection is possible, it makes it more difficult.
  4. Reads data tells MySQL you are only reading data and not updating information
  5. Begin tells MySQL this is the start of the main procedure
  6. Creates a variable input for data that is supplied to the stored procedure
  7. The rest is the same as previously discussed
Next, we need to give permission to the user to run the procedure.
mysql>;;Grant execute on get_id to 'me'@'localhost'

To run the stored procedure from the MYSQL console, you do the following:

mysql>;;call get_id('mycomputer', @out);
+-------+
 | @out |
 +------+
 | 1    |
 +------+

Once you have the output you want from the procedure, you can now call it from any program you want. Some of the more common languages to call stored procedures are: PHP, ASP and VB.net