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.

Answer 1

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.