Best practices to escape string in AWS RDS DataService with Aurora postgres serverless

Question

I read from: https://aws.amazon.com/blogs/database/using-the-data-api-to-interact-with-an-amazon-aurora-serverless-mysql-database/

The RDSDataService client also supports parameterized queries by allowing you to use placeholder parameters in SQL statements. Escaped input values permit the resolution of these parameters at runtime. Parameterized queries are useful to prevent SQL injection attacks.

But when I use it with Postgres, pass string: myname's and it breaks my SQL syntax. I don't sure how RDSDataService deal with SQL injection attacks as they written in document.

Could anyone can help me explain this? and how to deal safe SQL String in this case?

UPDATED: Sorry for my bad. RDSDataService already escaped string literal when using Parameterized queries.

Answer 1

Here is some basic code to take return values from Redshift or Aurora and transform it to insert into the database in a batch parameterSet:

Take your response including the metadata and pass that into this function. It will parse as strings or ints. If you need more datatypes supported you will have to create more if statements in the function below:

const data =
{
    "ColumnMetadata": [
        {
            "isCaseSensitive": true,
            "isCurrency": false,
            "isSigned": false,
            "label": "dealer_name",
            "length": 0,
            "name": "dealer_name",
            "nullable": 1,
            "precision": 255,
            "scale": 0,
            "schemaName": "raw_data",
            "tableName": "xxxxxxxxxxxxxxxxx",
            "typeName": "varchar"
        },
        {
            "isCaseSensitive": true,
            "isCurrency": false,
            "isSigned": false,
            "label": "city",
            "length": 0,
            "name": "city",
            "nullable": 1,
            "precision": 255,
            "scale": 0,
            "schemaName": "raw_data",
            "tableName": "xxxxxxxxxxxxxxxxx",
            "typeName": "varchar"
        },
        {
            "isCaseSensitive": false,
            "isCurrency": false,
            "isSigned": true,
            "label": "vehicle_count",
            "length": 0,
            "name": "vehicle_count",
            "nullable": 1,
            "precision": 19,
            "scale": 0,
            "schemaName": "",
            "tableName": "",
            "typeName": "int8"
        }
    ],
    "Records": [
        [
            {
                "stringValue": "Grand Prairie Ford Inc."
            },
            {
                "stringValue": "Grand Prairie"
            },
            {
                "longValue": 18
            }
        ],
        [
            {
                "stringValue": "Currie Motors Ford of Valpo"
            },
            {
                "stringValue": "Valparaiso"
            },
            {
                "longValue": 16
            }
        ]
    ],
    "TotalNumRows": 2
}

const buildParameterSets = (res) => {
  let columns = res.ColumnMetadata.map((c) => [c.name, c.typeName] );//get type and name of column
  let data = res.Records.map((r) => {
    let arr = r.map((v, i) => {
      if (columns[i][1].includes("int")) {
        return {
          name:   columns[i][0],
          value:  {
            longValue: Object.values(v)[0]
          }
        }
      } else {
        return {
          name:   columns[i][0],
          value:  {
            stringValue: Object.values(v)[0]
          }
        }
      }
    });
    return arr;
  });
  return data;
};

console.log(buildParameterSets(data));

Then you can insert using the BatchExecuteStatementCommand from the AWS SDK:

https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/clients/client-rds-data/classes/batchexecutestatementcommand.html

const rds_client = new RDSDataClient({ region: "us-east-2" });
let insert_sql = `INSERT INTO dealer_inventory (
  dealer_name,
  city,
  vehicle_count
  ) VALUES (
    :dealer_name,
    :city,
    :vehicle_count
  )`;

try {
// insert data
  const insert_params = {
    database: "dev",
    parameterSets: parameterSets,
    sql: insert_sql,
    secretArn: process.env.SECRET_ARN,
    resourceArn: process.env.RESOURCE_ARN,
  };

  const insert_command = new RDSBatchExecuteStatementCommand(insert_params);
  var insert_response = await rds_client.send(insert_command);

} catch (error) {

  console.log("RDS INSERT ERROR");
  console.log(error.message);

} finally {

  console.log("Inserted: ");
  console.log(insert_response);

}