how can i send text from a rich text editor into a sql database?-CodePudding

I am working on a react app, and this react app uses a rich text editor from a library called Mantine. This RTE outputs data wrapped into html tags. When i send that data into my sql database it gets rejected saying:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ql-align-justify">tex text tex texttex texttex texttex texttex texttex texttex t' at line 1

This happens when there are line breaks into the text or text modification(so it creates one or multiples
tags. I didn't check if it does with all the other controlls like bold and italic but it probably does. I know it's not safe to send this kind of data into an sql database. But how should i procede to send the article safely( with out without the html tags) and display them with the right form (memorize the bolds and the line breaks etc.)?

i'm using react, nodejs express for the server with mysql2, and the MySQL workbench.

This is the server side code that intercepts the code and sends it to the database

exports.createPost = (req, res, next) => {
  console.log(req.body)
  const data = `"${req.body.user_id}", "${req.body.title}", "${req.body.article}", "${req.body.tags}","${req.body.author}", "${req.body.date}"`
  const stmt = `INSERT INTO groupomania_social.articles(user_id,title,article,tags,author,date)VALUES(${data})`
  con.query(stmt, (err, results, fields) => {
    if (err) {
      return console.error(err.message)
    }
    return res.status(200).json({ message: 'new article created' })
  })
}

CodePudding user response：

Instead of composing the query as a string, use parametrized queries:

conn.execute('Select name from employee where id = ?', [id]);

This treats your data/parameters as separate from the query text, and also protects you against SQL injection.

CodePudding user response：

@farooq 's comment helped, at first it wasn't working because i buffered the entire article(author, user_id etc...) when i needed to buffer the actual article (coming from the text editor). the whole article object with the author and the rest of the info is too large in size.

here is the code

// create a buffer
  const buff = Buffer.from(req.body.article, 'utf-8')
  // decode buffer as Base64
  const base64 = buff.toString('base64')

  const data = `"${req.body.user_id}", "${req.body.title}", "${base64}", "${req.body.tags}","${req.body.author}", "${req.body.date}"`
  const stmt = `INSERT INTO groupomania_social.articles(user_id,title,article,tags,author,date)VALUES(${data})`
  con.query(stmt, (err, results, fields) => {
    if (err) {
      return console.error(err.message)
    }
    return res.status(200).json({ message: 'new article created' })
  })

So when the server gets the request you create a "safe" string with it, send it to the database, then you decode when you receive it, and send it to the front.

Parametized queries could probably work but the query sentence is a pain to figure out in this situation.

thanks everyone who tried to help, i hope this helps someone later. (still not recommended to render html from string in react)