STORE PROCEDURE SECURITY THE RISKS OF SQL INJECTION
The flexibility and ease of store procedures pays off in terms of security. We have created a flexible and dynamic system but one that exposes us to what is called Sql Injection, a much feared hacker attack technique. One way to reduce this risk is to decrease the number of parameters that are passed in. Another way to avoid malicious code is to minimize the permissions of the user using the Store Procedure. When we set up the user accessing the database we gave db_owner permissions, this is fine for a teaching course, but to be avoided at all costs when there is an application in production. db_owner basically means that that user is the owner of the database, so he can do anything. To reduce risk, you could assign permissions to db_datareader for reading and db_datawriter if it needs to edit data. Unfortunately, doing so would not give the user permission to perform the store procedures.
LIMIT THE RISK OF SQL INJECTION
To increase the security of our Store Procedures one can create a scalar function that somehow parses the values passed in the parameters and eliminates all potentially harmful characters. The function uses SQL Server’s REPLACE statement. The item-selection SP uses this function in the @filter and @orderby parameters and somehow cleans the passed values of all those characters that are considered harmful to security before the parameters are used.
THE METHOD OF ARTICLE ELIMINATION
The implementation of article deletion we will do after the JSP page for insertion has been built. If we do not want to run into Sql Injection we should not use concatenated strings but parameters. We see a simple solution using parameters. The question mark is a parameter and is not subject to Sql Injection.
MYSQL CHANGES TO THE PROJECT ALPHASHOP
Let us see what are the main changes to be made to the AlphaShop project on DBMS MySQL. We open the file pom.xml, the main change is to introduce the driver for MySQL.
The rest of the dependencies remain unchanged. Now let’s go to the application.properties file and modify it as follows. That file or rather the url refers to my machine on which I have installed MySQL listening on port 3306.
CREATING A USER WITH MINIMAL PERMISSIONS IN MYSQL
Now with a short video I show you how to create the WebClient user in MySqlWorkbench. As you will see I will assign minimal permissions to this new user. CRUD operations and execution Store Procedures. If you need additional permissions enter with the root user and assign additional permissions.
Another important difference concerns the persistence layer, we have only one selection method as the order by was implemented at the service level using lambdas in fact MySQL does not support dynamic queries.
Let us see at the repository level how the interface has been implemented.
In MySQL the characters are case-sensitive, in fact in the deletion method the table is called articoli all lower case, we would have had problems if we left the table name in uppercase. We used T-SQL code directly in the code by parameterizing the item code with a question mark to prevent Sql Injection. Let us look at the service layer, in this case in the interface we have the two sorting methods since as already mentioned sorting is implemented via lambdas.
Let’s look at a piece of code implementing the interface.
CREATION OF THE STORE ARTICLE SELECTION PROCEDURES
We’ve already seen the logic for SQL Server, it changes the syntax a bit. I report the code.
DOWNLOAD ARTICLE CODE
The AlphaShopV2.zip project is for the SQL Server DBMS while AlphaShopV3.zip is for MySQL. Below I show you the Store Procedures to modify, first those for SQL Server then those for MySQL.
Leave A Comment