How to use an HTTP request to add varchar values to a MySQL query in NodeJS-CodePudding

Hi I'm currently using Express.js and Node.js to construct a test backend. It has a MySQL database connection. I want to use Postman to POST a data set into the user column. All the functions are configured, however, I'm having trouble utilizing javascript to build the MySQL query runtime.

My HTTP post request from the postman is seen above.

async function create(user){
    const result = await db.query(
      `INSERT INTO user 
      (username, password, fullname, type, gender, dob, address, email, contact_no) 
      VALUES(
        ${user.username}, 
        ${user.password}, 
        ${user.fullname}, 
        ${user.type}, 
        ${user.gender}, 
        ${user.dob}, 
        ${user.address}, 
        ${user.email}, 
        ${user.contact_no}
        )`
    );
  
    let message = 'Error in creating the user!';
  
    if (result.affectedRows) {
      message = 'user created successfully!';
    }
  
    return {message};
  }

The javascript function I used to add a user to the database is shown above. But the situation is that the query should be

INSERT INTO user
  (username, password, fullname, type, gender, dob, address, email, contact_no) 
  VALUES(
    "Madhawa",
    "asdfg",
    "Madhawa_Cooray",
    "CUSTOMER",
    "MALE",
    "2000-11-10",
    "Colombo",
    "madawasahasgmail.com",
    "0778923476"
    );

I'm getting the following MySQL query in the Node.js runtime

INSERT INTO user 
  (username, password, fullname, type, gender, dob, address, email, contact_no)
  VALUES(
    Madhawa,
    asdfg,
    Madhawa_Cooray,
    CUSTOMER,
    USER,
    2000-11-10,
    Colombo, Dehiwala,
    madawasahasgmail.com,
    0778923476
    )

So I'm getting the following error on MySQL,

ERROR 1054 (42S22): Unknown column 'Madhawa' in 'field list'

Which section of my code should I modify? Thank you :)

CodePudding user response：

First of all, don't ever put user input directly in the SQL request. It is a very high security risk, granting any malicious user the opportunity to execute SQL injections.

Depending on which package you're using to connect to the database, there should be a "prepare" method, to create statements in which you put placeholder characters such as question marks where your values will go.

Then in the execute method you'll pass an array or object containing the actual values. The package will make sure that you inputs are well formatted and cannot allow injections to happen.

If you are using the mysql package, there are methods to escape query values (see Escaping query values). You can also put questions marks and use the second arguments of db.query to pass in the values.

For example, your query, would look something like this:

const result = await db.query(
    "INSERT INTO user (username, password, fullname, type, gender, dob, address, email, contact_no) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", [
        user.username,
        user.password,
        user.fullname,
        user.type,
        user.gender,
        user.dob,
        user.address,
        user.email,
        user.contact_no
    ]
);