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:
- Sets the name of the statement to “id”
- Sets the SQL statement and has a place holder for the name in the query
- Sets the variable @myname to “mycomputer”
- Executes the statement “id” using the variable @myname as the query value ‘?’
- 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$$
- If the procedure already exists, delete it
- Setup the delimiter for starting and stoping the procedure text
- 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.
- Reads data tells MySQL you are only reading data and not updating information
- Begin tells MySQL this is the start of the main procedure
- Creates a variable input for data that is supplied to the stored procedure
- The rest is the same as previously discussed
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 | +------+