CODEWITHSUNDEEP
mysqlnode.jsreactjs
daniel sas
daniel sas

Reputation: 483

MySQL placeholders throw error on WHERE clause

I am using MySQL placeholders and create promise to use them with async await.

selectTickets: (variable) => {
    const sqlStatement = `SELECT * FROM tickets WHERE userID = ?`;
    return new Promise((resolve, reject) => {
        db.query(sqlStatement, variable, (error, response) => {
            if (error) return reject(error);
            return resolve(response);
        });
    });
},

i tried even to create the statement with interpolation and gave me an error: "Unknown column 'undefined' in 'where clause'"

This is my code. But when I am using it in react, I am getting Error 500 status saying that the statement is incorrect.

ode: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1",
  sqlState: '42000',
  index: 0,
  sql: 'SELECT * FROM tickets WHERE userID = ?'

I tried alot of other methods. But other functions that uses same type of function with same type of statement are working just fine.

In react I have this call:

 const URL_TICKETS_BY_USERID = 'http://localhost:3000/api/get-tickets';
  const config = {
    headers: { Authorization: `Bearer ${user.token}` }
};
  const userID = user.userID;

  axios.get(URL_TICKETS_BY_USERID,
        userID,
        config
    )
    .then(data => console.log(data))
    .catch(error => console.log(error))

Can i have some help?

Upvotes: 1

Views: 775

Answers (2)

shamnad sherief
shamnad sherief

Reputation: 646

In my case it was the question mark. In some database systems, the question mark is used as a placeholder for values that are passed in separately. However, it seems that in my case, the database system is not recognizing the question mark as a placeholder, and is interpreting it as part of the query. So changed the query to:-

const q = `SELECT * FROM list WHERE userid = ${listId}`;

I passed the variable inside the query and it works now

Upvotes: 0

Henrik Erstad
Henrik Erstad

Reputation: 691

The problem lies with your db.query() call. The second parameter should be an array, even for single values. This should work:

db.query(sqlStatement, [variable], (error, response) => {
  if (error) return reject(error);
  return resolve(response);
});

Also axios get() takes two parameters: url and config (optional). This means that any params should be part of that config object:

const config = {
  headers: { Authorization: `Bearer ${user.token}` },
  params: {
    id: user.userID
  }
};
axios.get(URL_TICKETS_BY_USERID, config)

Alternatively pass it as a GET parameter in the URL:

axios.get(URL_TICKETS_BY_USERID + "?id=" + user.userID, config)

Upvotes: 2

Related Questions