create stored procedure in sql which can take multiple arguments-CodePudding

I am trying to make basic login/registration system using express.js and mysql. In first prototype I used string queries to enter and retrieve data from database. Now I want to upgrade it and use stored procedure as someone on stackoverflow told me that providing queries as string makes system vulnerable to sql injection. My query contains two parameter provided from user by registration or login forms.

Here are queries

    var username, password;
    var userInfoArray = [username, password]
    var registrationQueryString = "INSERT INTO userinfo (username, password) VALUES ( ?, ? )"
    var loginQueryString = "SELECT username, password FROM userinfo WHERE EXISTS (SELECT username, password FROM userinfo WHERE userinfo.username = ( ? ) AND userinfo.password = ( ? ))"
    
    function userRegistration (userInfoArray){
        dbConnection.query( registrationQueryString, userInfoArray, function(err, results, fields) {
            if (err) throw err
            console.log("registered new user")
        })
    }
    function userLogin (userInfoArray){
        dbConnection.query( loginQueryString, userInfoArray, function(err, results, fields) {
            if (err) throw err
            console.log(results)
        })
    }

I want to make stored procedure equivalent to the code I provided.

I found too many answer about this same question but all of them are too old (most of 4-5 years old ) ; still I tried to use many but none of them worked.

also I tried to look on official documentation but I didn't completely understand.

CodePudding user response：

First you need to store procedures on the database

CREATE PROCEDURE RegisterUser @username varchar(30), @password varchar(10)
AS
BEGIN
INSERT INTO userinfo (username, password) 
VALUES (@username, @password)
END;
________________________________________________________________________________________
CREATE PROCEDURE LoginUser @username varchar(30), @password varchar(10)
AS
BEGIN
SELECT username, password 
FROM userinfo 
WHERE EXISTS (
      SELECT username, password 
      FROM userinfo 
      WHERE userinfo.username = @username AND userinfo.password = @password
)
END;

Then you can use them in your code:

var username, password;
var userInfoArray = [username, password]
var registrationProcedure = "EXEC RegisterUser @username = $1, @password = $2"
var loginProcedure = "EXEC LoginUser @username = $1, @password = $2"
    
function userRegistration (userInfoArray) {
         dbConnection.query(registrationProcedure, userInfoArray,
         function(err, results, fields) {
            if (err) throw err
            console.log("registered new user")
        })
}

function userLogin (userInfoArray) {
         dbConnection.query(loginProcedure, userInfoArray,
         function(err, results, fields) {
            if (err) throw err
            console.log(results)
        })
}

Notice that using stored procedure doesn't mean you're no more passing parameters to the queries like you did before (string concat), just means you're hiding the query implementation on the database and this improves security.

Here's the reference I used for this answer.

This is just an example, but I would also avoid using the same name of the column for the input variables and store only the hash of a password on the db. So before saving during registration and before the comparison part WHERE ... AND userinfo.password = @password I would hash the password coming from the request.