How I can prevent the sql injection vulnerability when I generate sql scripts instead of directly accessing the database?

Question

For my job I need to generate a sql scripts for updating some values in the database from a json file as seen bellow:

$jsonContents=file_get_contents('./myfile.json');
$jsonContents=json_decode($jsonContents, true);

$script="";

foreach($jsonContents['data'] as $element)
{
  $script.="UPDATE mytable SET element='{$element['value']}' where id={$element['id']};";
}

file_put_contents('./script.sql',$script);

So I wonder once the sql script will be executed, is the danger of sql injection lurking around or is safe to just place the data as is into the sql script? If under some circumstances the sql injection threat is lurking and how I can make my script sql injection safe?

Keep in mind that I was asked to provide an sql script instead of directly accessing the database and updating the values using php. In the later case I could just use the PDO and prepared statements.

The database layer is postgresql.

Your code doesn't protect against anything, consider a value containing a single quote and how your SQL will be generated. That is not even a SQL injection problem. I would clarify with whoever asked you to generate the script your concerns and let them make an (informed) decision of the potential problems (and get the answer in writing). — Nigel Ren
– Nigel Ren, Commented Nov 22, 2019 at 8:16
The question is on how I can generate sql-injection safe scripts because I cannot access prepared statements offered by PDO once I generate sql scripts. — Dimitrios Desyllas
– Dimitrios Desyllas, Commented Nov 22, 2019 at 8:21
You will need to escape the values using database-specific escape functions, e.g. mysqli_real_escape_string. The thing is, those aren't 100% safe either, but to have any chance for them to work even remotely reliably you need to have an active database connection to the database you want to insert the data into. So… is that possible? Probably not, right? — deceze
– deceze ♦, Commented Nov 22, 2019 at 10:34
@Raymond Sorry, momentarily overlooked the tag. Substitute with the appropriate Postgres equivalent… — deceze
– deceze ♦, Commented Nov 22, 2019 at 10:39

Dimitrios Desyllas · Accepted Answer · 2019-11-22 10:20:24Z

2

Usually many DBMS'es have sql code for generating prepared statements: Eg. for Postgresql here it is: https://www.postgresql.org/docs/current/sql-prepare.html

So you could change your script into:

$jsonContents=file_get_contents('./myfile.json');
$jsonContents=json_decode($jsonContents, true);

$script="PREPARE myquery (text,int) AS UPDATE mytable SET element=$1 where id=$2;EXECUTE myquery";

foreach($jsonContents['data'] as $element)
{
  $script.="($${$element['value']}$$,{$element['id']})";
}

$script.=";";

file_put_contents('./script.sql',$script);

So in this case you need manually to generate the correct query to perform a prepared query instead of relying on the PDO to do that. Also consinder using the $$ delimiter in order to avoid roque ' to mess your update.

For mysql similary you can use the following query format: https://dev.mysql.com/doc/refman/5.7/en/sql-prepared-statements.html

In case you dynamically generate the query then, you need to create multiple prepared statements depending on the situation for example the $element['value'] is a comma separated value that need to be split into chunks and each value represent a different column.

edited Nov 22, 2019 at 10:20

answered Nov 22, 2019 at 10:14

Dimitrios Desyllas

10.4k17 gold badges89 silver badges203 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ususipse Over a year ago

You would end up with the same vulnerability once you create the file. The PREPARE function will sanitize the string you are saving but you will still need to PREPARE it again when you do the actual update.

Dimitrios Desyllas Over a year ago

Ok the vulnerability exists that a malicious dev could just edit manually the sql script but in this case you can also use manual non-coding methods to do so. Eg. have a responsible person or have 2 devs executing the script on same pc or even having a security oficcer/DPO to check it first etc etc.

Dimitrios Desyllas Over a year ago

The only thing I can think of is that a -- (comment indicator) can mess my script.

Raymond Nijland Over a year ago

Assuming you don't have a malicious dev/admin or a irresponsible or somebody that not has his/her day and make mistakes this should be safe.. If it is really needed you could program an encryption/decryption layer on top of it with SQL/MED to it prevent it from running on the production RDMS until verified by that security officer/DPO that there is non malicious code and gives that import his password to encrypt and execute on his pc..

Raymond Nijland Over a year ago

or handle that import based on certificate permissions on (some trusted) PostgreSQL users, which feels like a better idea

Collectives™ on Stack Overflow

How I can prevent the sql injection vulnerability when I generate sql scripts instead of directly accessing the database?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related