Safe way to store SQL query in database column

There are many instances where we have to store SQL Queries in database.

It could be anything of the below or even some other usecase:

• A job to be run on a query.
• Store query to be referenced later.
• A custom query from an engineer to help them automate task based on query.
• or something else.

The problem with storing a query inside a column is SQL Injection or SQLi. As this time we purposely want to store a query inside a query that too coming from someone who knows how to form queries.

But how?

Imagine you have table future_jobs_data and one of the column as backup_sql which will hold the query and will be used to run some jobs in future. The easiest way to furm such query is:

INSERT INTO future_jobs_data
(job_name, backup_sql, run_date, run_time)
VALUES
("Job-1", "select * from abc", "12-3-2022", "12:00");

Now imagine some user entering backup_sql as

"); DROP DATABASE db_name; --

Now put this in your query:

INSERT INTO future_jobs_data
(job_name, backup_sql, run_date, run_time)
VALUES
("Job-1", ""); DROP DATABASE db_name; -- ", "12-3-2022", "12:00");

This will result in breaking your query into 2 queries and one comment. where 2nd query is the malicious one.

How to avoid it?

Best way is to store any query after encryting it. Do not give raw query inside a column.

Assume this to be your server code which receives the input and form a query:

let query = "INSERT INTO future_jobs_data
 (job_name, backup_sql, run_date, run_time)
 VALUES
 (
   ${job_name},
   ${backup_sql},
   ${run_date},
   ${run_time}
 );"
db.insert(query);

Here just encrypt the backup_sql or even other variables before inserting into the table.

let query = "INSERT INTO future_jobs_data
 (job_name, backup_sql, run_date, run_time)
 VALUES
 (
   ${job_name},
   ${encryptWithKey(backup_sql)},
   ${run_date},
   ${run_time}
 );"
db.insert(query);

Also after fetching the data decrypt with the same key and perform the action with the query.