1

I'm trying to store texts into a table, like this:

let insert_query = "insert into Messages(mid, mdate, sender, mtype, reply_to, is_forwarded, mtext, size, resolution, duration, emoji)"
insert_query += `values ('${message_id}', to_timestamp('${message_date}', 'DD.MM.YYYY HH24:MI:SS'), '${from_name}', '${media_type}', '${reply_to}', ${is_forwarded}, '${text}', '${media_size}', '${photo_resolution}', '${media_duration}', '${sticker_emoji}');`

This is a rather long query, the important part to notice is where I add '${text}' into the mtext field.

I received this error:

{ error: syntax error at or near "s"
    at Connection.parseE (/home/amirashabani/Amir/Code/telyzer/node_modules/pg/lib/connection.js:602:11)
    at Connection.parseMessage (/home/amirashabani/Amir/Code/telyzer/node_modules/pg/lib/connection.js:399:19)
    at Socket.<anonymous> (/home/amirashabani/Amir/Code/telyzer/node_modules/pg/lib/connection.js:121:22)
    at Socket.emit (events.js:198:13)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
  name: 'error',
  length: 91,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '251',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1134',
  routine: 'scanner_yyerror' }

I printed the insert_query for when these errors happen, and here are some of the results:

insert into Messages(mid, mdate, sender, mtype, reply_to, is_forwarded, mtext, size, resolution, duration, emoji)
values ('message8224', to_timestamp('21.06.2019 21:27:34', 'DD.MM.YYYY HH24:MI:SS'), 'AmirAShabani', 'text', '', false, 'he's a loser', '', '', '', '');

I can see that the problem is with the single quote character. I tried to replace it with \', like this: 

text = child['children'][0]['data'].trim().replace('\'', '\\\'')

But that doesn't help either, and I'm not quite sure I should do that to begin with.

How can I fix it?

Amir Shabani
  • 3,857
  • 6
  • 30
  • 67
  • 1
    Can you `console.log(insert_query)` and add the result to your question? –  Jul 10 '19 at 09:43
  • @ChrisG Edited, now I know what the problem is, but I don't know how to solve it. – Amir Shabani Jul 10 '19 at 09:54
  • Shouldn't one escape `'` (single quote) by replacing it with two single quotes in Postgres? And why don't you use [parameterized query](https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example#parameterized-queries) instead? – raina77ow Jul 10 '19 at 09:57
  • 1
    Use a parametrized query. This also prevents injections. `connection.query("insert into Messages (mid, mdate) values (?, ?)", [message_id, message_date])` –  Jul 10 '19 at 09:58

0 Answers0