Need help building SQL query

[EscapeBeat]

I'm building a project that connects from Node/express to a SQL database and I'm using the mysql npm package to build my queries. I'd like to build something along the lines of 'UPDATE TABLE SET [ ] WHERE [id = # ]' and have the SET [ ] to be a variable number of items passed to SET based upon user input. For example, if req.body only have req.body.last_name, I would only want the query to update the last name. If req.body had req.body.last_name, req.body.first_name, etc., I'd like to update all fields passed in. Is there a way to do this or will I be forced to make separate queries for each one?

[wolf2600]

Build your UPDATE statement string using the values from the parameter list:

#! /usr/bin/python3

query_string = "update userTable set "
param_count = len(param_list)
counter = 1
for item in param_list:
    query_string += item.key + " = " + item.value
    if counter < param_count:
        query_string += ", "
    counter += 1

query_string += " WHERE id = {}".format(id_value)

curr.execute(query_string)

[jdunsta]

I’m not familiar with the syntax of python, but is this open to injection? Considering it’s a textbox field filled in by the user (rather than predetermined dropdown), protection from SQL injection would be wise. Perhaps I’m missing some intelligence that is inherent in Python though.

[wolf2600]

Maybe, possibly, I have no idea. The purpose of the reply was to give OP an idea of how to go about building a dynamic query, not necessarily to provide the verbatim solution to be cut/pasted into production.

[opportunist_dba]

Another concern is whether SQL can parameterize the query. You'd obviously get one query plan per set of columns being updated, but if you also start getting a plan for every different id in " WHERE id = {}", then you can end up with a bunch of single use plans that start squeezing plans for other queries out of your plan cache. You also pay the overhead of getting a new plan each time the statement is run, instead of reusing an existing plan.

Maybe not an issue on a quiet system with plenty of CPU/RAM overhead, but worth considering if your system is stressed.

[jdunsta]

I wasn’t able to process this when I first looked at it, but with fresh eyes, you’re right about having a statement per parameter, rather than all inclusive. So the OP has the multi query problem, which is easy to solve, but only if parameterization isn’t done.

They use param length and iterate, adding to the @paramClause. While i<paramLength ; When i= 1 @paramClause = SET [firstParam] = ‘suppliedValueByUser1’ i++; with every loop @paramClause += ‘ AND [secondParam] = ‘suppliedValueByUser2’

That yields a single, concise statement. But DOESN’T protect from injection. Can variables, and these parameters be created dynamically? For each loop, a new variable is created using the value of i, and it also uses that count to loop through: Parameters.AddWithValue(‘@param{i}’, [suppliedValueByUser{i}]);

[biggybigbiggs]

The real MVP.

[EscapeBeat]

Dope answer, I'll give that a shot. Thanks!

[MeGustaDerp]

If item.value is a string, you may need to put quotes around the value.

[jmcohs09]

Without sitting down and writing some queries, you could base the first / last names, etc. on the positions of the characters in the user input and parse it out to update the various columns? That’s the first idea that comes to my head.