How do i "SET" and "REMOVE" using the same UpdateExpression when querying DynamoDB, while keeping the query safe of "SQL injections" [python/boto3]

0

[using python/boto3]

I need help writing a (hopefully simple) DynamoDB query that accepts a mode_id input variable and a list of algorithm_indexes [ints], then go to that row in the DB, remove from the "algorithms" attribute (of type List) the specified indexes, and update "last_change" attribute with the timestamp variable.

Example => Inputs: mode_id = 3 algorithm_index = [0,2]

So if this is my row in DynamoDB:

{
  "mode_id": {
    "N": "3"
  },
  "algorithms": {
    "L": [
      {
        "S": "test1"
      },
      {
        "S": "test2"
      },
      {
        "S": "test3"
      }
    ]
  },
  "created": {
    "N": "1680025806"
  },
  "last_change": {
    "N": "1682959324"
  },
  "version": {
    "S": "v666"
  }
}

**The algorithms List in DynamoDB result should be only ["test2"] and the "last_change" value changes to the current timestamp. **

The final result for the UpdateExpression should be (i think):

REMOVE algorithms[0], algorithms[2]; SET last_change=123123

This is the code i started writing:

# get current timestamp 
timestamp = Decimal(int(time()))

# indexes to be removed: 
update_expression = "REMOVE "
expression_attribute_values = {}
expression_attribute_names = {}
index = 0
for v in algorithm_index:
    index += 1
    update_expression += f"#alg{index}, "
    expression_attribute_names[f"#alg{index}"] = f"algorithms[:val{index}]"
    expression_attribute_values[f":val{index}"] = v
# remove ", "
update_expression = update_expression[:-2]

print(f"update_expression: ", update_expression)
print(f"expression_attribute_names: ", expression_attribute_names)
print(f"expression_attribute_values: ", expression_attribute_values)

# concatenate timestamp to the "UpdateExpression"
update_expression += f"; SET last_change={timestamp}"

# update query 
table.update_item(
    Key={'mode_id': mode_id},
    UpdateExpression=update_expression,
    ExpressionAttributeNames=expression_attribute_names,
    ExpressionAttributeValues=expression_attribute_values,
    ReturnValues='UPDATED_NEW'
)

Prints should look like:

update_expression: REMOVE #alg1, #alg2
expression_attribute_names: {'#alg1': 'algorithms[:val1]', '#alg2': 'algorithms[:val2]'}
expression_attribute_values: {':val1': 0, ':val2': 2 }

Some highlights:

  • The query should be secure so no one can "SQL inject" malicious inputs, that's why the "ExpressionAttributeValues" is for, right? (i know it's not SQL! i just don't know what other term there is for "sql injection" for this case).
  • I want to remove a list of items (that's why i use the for-loop), but at the end i want to use a "SET" to update the "last_change" column too.
  • I'm splitting between the "REMOVE" and "SET" using a ";" (is this right?)
  • Since removing list items nest the value ("REMOVE attribute[index]"), i nested it in the the "ExpressionAttributeNames" too, which lead to the value in "ExpressionAttributeValues".
  • i didn't use any "ExpressionAttributeNames/Values" for the timestamp since it is self generated and nothing can tamper with it.

Is it done well? Thanks!

asked a year ago294 views
1 Answer
1

SQL injections in DynamoDB are not the same as a relational database. DynamoDB doesn't execute any code, so the injections would only be to remove something in the item, that item being scoped to the individual item you are actioning, meaning there is a very small blast radius.

I'm splitting between the "REMOVE" and "SET" using a ";" (is this right?)

No, it should just be split with a space

REMOVE #alg1, #alg2 SET last_change = :y

profile pictureAWS
EXPERT
answered a year ago
  • I think i wasn't so clear with my statement...

    i have some parts in my code where i concatenated stuff directly to the UpdateExpression like:

    update_expression = f"REMOVE algorithms[{user_input_var_here}]"

    Which i guess is the "bad practice". (please correct me if im wrong here) That's why i started using "ExpressionAttributeValues", to apply this protection ( <- by escaping sensitive strings?)

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions