Using MySQL stored procedures with Javascript objects

Question

I am hoping to receive guidance on how to pass a mySQL stored procedure Javascript form data to use as parameters in a query.

I have been searching for an answer on how to use mySQL stored procedures with javascript form data as parameters.

This is what I have thus far:

    var first_name = req.body.firstName,
        last_name= req.body.lastName,
        email= req.body.email,
        password= req.body.password,
        gpa= req.body.gpa,
        major = req.body.major,
        classification= req.body.classification;
    var query = connection.query("CALL new_student()", function (err, result) {
        if (err) {
            res.send(err);
        }
        res.json(result);
    })

Here is the stored procedure:

     CREATE DEFINER=`root`@`localhost` PROCEDURE `new_student`(IN first_name VARCHAR(45), 
IN last_name VARCHAR(45), IN email VARCHAR(45), IN password VARCHAR(45), IN gpa DECIMAL(3,2),
 IN major INT(10), IN classification VARCHAR(45))
BEGIN
INSERT INTO users (first_name, last_name, email, password)
 VALUES (first_name, last_name, email, password);
INSERT INTO student (user_id, gpa, major, classification)
 VALUES (LAST_INSERT_ID(),gpa, major, classification);
END

My intention is to take the variables, or a Javascript object that encapsulates the variables, and pass them in through "new_student()."

I am aware this may seem trivial. I am in the process of learning how to use stored procedures with Javascript.

The new_student() function has several parameters. You're not providing any parameters when you call it. — Barmar
– Barmar, Commented Apr 25, 2016 at 23:18

Community · Accepted Answer · 2017-05-23 12:31:13Z

2

You need to provide arguments to the function. If you're using the node-mysql-native you can provide the parameters using syntax like a prepared statement.

var query = connection.query("CALL new_student(?, ?, ?, ?, ?, ?, ?)", [first_name, last_name, email, password, gpa, major, classification], 
    function (err, result) {
    if (err) {
        res.send(err);
    }
    res.json(result);
})

For more information about this, see Preventing SQL injection in Node.js

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Apr 25, 2016 at 23:22

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mapage2 Over a year ago

I appreciate it tons!

Collectives™ on Stack Overflow

Using MySQL stored procedures with Javascript objects

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related