用Aurora无服务器转义AWS中字符串的最佳实践

Question

我读到：https://aws.amazon.com/blogs/database/using-the-data-api-to-interact-with-an-amazon-aurora-serverless-mysql-database/

通过允许在RDSDataService语句中使用占位符参数，RDSDataService客户端还支持参数化查询。转义的输入值允许在运行时解析这些参数。参数化查询对于防止SQL注入攻击非常有用。

但是当我在Postgres中使用它时，传递string：myname's，它破坏了我的SQL语法。我不确定RDSDataService如何处理在文档中编写的SQL攻击。

有人能帮我解释一下吗？以及在这种情况下如何处理安全的SQL字符串？

更新：对不起我的错。当使用参数化查询时，RDSDataService已经转义字符串文本。

Answer 1

下面是从Redshift或Aurora获取返回值的一些基本代码，并将其转换为在批处理parameterSet中插入数据库：

接受您的响应，包括元数据，并将其传递到此函数中。它将解析为字符串或It。如果需要支持更多的数据类型，则必须在下面的函数中创建更多If语句：

​

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));

​

然后可以使用AWS中的BatchExecuteStatementCommand插入：

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);

}