How to specify "greater than" parameter in an API call to an AWS Lambda (set with AWS SAM and with a MySQL DB)

I'm having trouble in trying to allow comparison criteria other than = in my function. I have a get function that allows me to retrieve all rows from my DB given specific criteria, for example :

GET http://127.0.0.1:3000/package?size=10

But I want to get them with a greater than criteria, like :

GET http://127.0.0.1:3000/package?size>8

But when doing so, I get the error "Unknown column 'size>8' in 'where clause'" because the > can't be parsed, only the = can be. It is due to the event parsing that gives me "queryStringParameters":{"size>8":""} in my event variable, when the first one gives me "queryStringParameters":{"size":"10"}, generating the following MySQL query

SELECT * from `package` WHERE `size>8`=''

Instead of

SELECT * from `package` WHERE `size`>8

Do you know how I can configure my environment in order to get the correct event variable ? I think it's related to my template.yaml file but I cant find where (maybe in the RequestParameters ?).

Solution

Its unsafe to take SQL operators directly from API clients due to SQL injection attacks. One approach that could improve your security posture is to add another parameter on your API to accept the operation you'd like to perform (eq, neq, gt, lt, lte, gte):

# Size = 10 (eq -> =)
GET http://127.0.0.1:3000/package?op=eq&size=10

# Size > 10 (gt -> >)
GET http://127.0.0.1:3000/package?op=gt&size=10

# Size < 10 (lt -> <)
GET http://127.0.0.1:3000/package?op=lt&size=10

You can do this in typescript by writing something that looks like:

const opsMap = new Map<string, string>();

opsMap.set("eq", "SELECT * from `package` WHERE `size` = ");
opsMap.set("gt", "SELECT * from `package` WHERE `size` > ");
opsMap.set("lt", "SELECT * from `package` WHERE `size` < ");

function getQuery(op: string, size: number) : string {
   if (opsMap.has(op)) {
     return opsMap.get(op) + size.toString();
   }

   throw new Error("Unknown operator");
}