which method to follow to prevent SQL injection in MySql Stored Procedure

Question

hi friends i googled for this and find different methods use by others to prevent sql injection. i wrote in below stored procedure before finalising to follow specific method i want suggestion from you guys. which method should i follow.

below is the example of my stored procedure, in which i wrote different methods

    CREATE DEFINER=`root`@`localhost` PROCEDURE `spTestSQLInjection`(pSelfId VARCHAR(100),bIntSelfId BIGINT(20))
BEGIN
   
    SET @sSelfId = pSelfId;
   
    -- Method:1
    -- below code is for injection
    SET @selectQuery = CONCAT('select * from userProfile where userId = ',@sSelfId);
   
    PREPARE stmt FROM @selectQuery;
    EXECUTE stmt ;
    DEALLOCATE PREPARE stmt;
   
   
    -- Method:2
    -- injection doesent affect below code
    select * from userProfile where userId = @sSelfId;
   
   -- Method:3
    select * from userProfile where userId = bIntSelfId;
    
    -- Method:4
    SET @sSelectQuery=
        'select * from userProfile where userId = ? ';
    PREPARE stmtQuery FROM @sSelectQuery;
        EXECUTE stmtQuery USING @sSelfId;
        DEALLOCATE PREPARE stmtQuery;
   
END

executed below stored procedure in workbench :

1)call spTestSQLInjection('231', 231);

result : when i pass proper data then result set gives single user data for all then 4 method.

2)call spTestSQLInjection('231 OR 1=1', 231);

result : when i pass '231 OR 1=1' data then result set gives all user data for method 1 and single record for method,2,3,4. so concluded that method1 is prone to sql injection so not to follow this method, as its dynamic query & its advisable not to write dynamic query in stored procedure.

method2, method3 worked & gave single user record, which means this query are not prone to sql injection.

method4 is adviced by most of the developer to follow this to prevent sql injection in stored procedure. but my live project contains 20 to 30 queries(insert/update/delete) inside a stored procedure, so writing prepared statement for all is time consuming.

so guide me to follow which method, method2, method3, or method4

Thanking you in advance, any help will be appreciated.

Answer 1

Methods 2, 3, and 4 are safe from SQL injection, but method 3 is the simplest solution.

CREATE DEFINER=`root`@`localhost` PROCEDURE `spTestSQLInjection`(pSelfId VARCHAR(100), bIntSelfId BIGINT(20))
BEGIN
   
   -- Method:3
    select * from userProfile where userId = bIntSelfId;
           
END

There's no need to create a user-defined variable, because the procedure parameter bIntSelfId is already a variable.

There's no need to use a parameter or a prepared statement in this case, because the variable is treated only as a scalar value. It doesn't need to modify any SQL syntax, nor is it used as an identifier, so it can simply be used in the query as shown above.

This assumes your table does not have its own column with the same name of bIntSelfId. If it did, the use of that identifier would be ambiguous. It's recommended to name your parameters distinctly from any of the columns of tables you will query using that variable. Using a user-defined variable or a query parameter would also avoid the ambiguity.