erlang: how to protect database against SQL injection inside json field-CodePudding

I am using epgsql(https://github.com/epgsql/epgsql) lib which allows writing queries like this:

  "INSERT INTO my_table"
  "(item_id, json_data) "
  "VALUES ($1, $2) "
  "ON CONFLICT "
  "DO NOTHING "
  "RETURNING *;",

And then call such queries with different parameters. In general, we expect a wide range of incoming data without any pre-defined format. The only thing we expect is that it's a JSON of the following format: {"field_name": "long value from the user with a potential injection of the SQL code I need to be protected from"}

The question I am having is how do I protect the query from something bad. E.g. that someone enters something like ; DROP table ... --- or anything like that?

CodePudding user response：

In the situation when you provide a service for DB queries really very hard to do a call cleanup.

Only one option coming to my mind:

Normalise the incoming command with upper case, removing of double-spaces, split a command into ";"-lines etc. and then you can potentially do a dictionary search for text as "DROP TABLE…" or "DELETE FROM…" to mark command as a dangerous and prevent execution, other cases can be also provided, but this creates a knowledge base to be maintained.

CodePudding user response：

I think you have to write it by yourself considering other language's implementation.