CVE-2022-4860: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: A vulnerability was found in KBase Metrics. It has been classified as critical. This affects the function upload_user_data of the file source/daily_cron_jobs/methods_upload_user_stats.py. The manipulation leads to sql injection. The patch is named 959dfb6b05991e30b0fa972a1ecdcaae8e1dae6d. It is recommended to apply a patch to fix this issue. The associated identifier of this vulnerability is VDB-217059.
CWE description: The product constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
CVSS v3 base score: 9.8
Published: 2022-12-30T12:15Z
Repository: https://github.com/kbase/metrics
Language: Python
Files changed: source/daily_cron_jobs/methods_upload_user_stats.py
Commit date: 2022-05-25 00:46:08 +0000
Commit message: fixed sql update to avoid sql injection

Diff: diff --git a/source/daily_cron_jobs/methods_upload_user_stats.py b/source/daily_cron_jobs/methods_upload_user_stats.py
index 0e15ee5..4abbeb9 100644
--- a/source/daily_cron_jobs/methods_upload_user_stats.py
+++ b/source/daily_cron_jobs/methods_upload_user_stats.py
@@ -608,40 +608,18 @@ def upload_user_data(user_stats_dict):
 
     print("Number of new users info inserted:" + str(new_user_info_count))
     print("Number of users updated:" + str(users_info_updated_count))
 
     dev_tokens_users = get_dev_token_users_from_mongo()
-    #print("dev_tokens_users: " + str(dev_tokens_users))
-
-    ####################
-    # TRIED DO UPDATE WITH PASSED LIST NONE OF THIS WORKED
-    # HAD To build up the entire string
-    #    update_new_dev_tokens_statement = (
-    #        "update user_info set dev_token_first_seen = now() "
-    #        "where dev_token_first_seen is null and "
-    #        "username in (%s)"
-    #        )
-    #    sql_params = ",".join(dev_tokens_users)
-    #    sql_params = (dev_tokens_users,)
-    #    sql_params = ([str(dev_tokens_users)])
-    #    cursor.execute(update_new_dev_tokens_statement, [sql_params])
-    #    cursor.execute("update user_info set dev_token_first_seen = now() "
-    #                   "where dev_token_first_seen is null and "
-    #                   "username in (%s)" % ', '.join('?' * len(dev_tokens_users)), dev_tokens_users)
-    #    update_new_dev_tokens_statement = (
-    #        "update user_info set dev_token_first_seen = now() "
-    #        "where dev_token_first_seen is null and "
-    #        "username in (%s)" % ', '.join('?' * len(dev_tokens_users)), dev_tokens_users
-    #        )
-    #    cursor.execute("SELECT foo.y FROM foo WHERE foo.x in (%s)" % ', '.join('?' * len(s)), s)
-    dev_tokens_string = "', '".join(dev_tokens_users)
     update_new_dev_tokens_statement = (
         "update user_info set dev_token_first_seen = now() "
         "where dev_token_first_seen is null and "
-        "username in ('" + dev_tokens_string + "')"
+        "username in (" + ("%s, " * (len(dev_tokens_users) - 1)) + "%s)"
         )
-    cursor.execute(update_new_dev_tokens_statement)
+#    print("update_new_dev_tokens_statement : " + update_new_dev_tokens_statement)
+    update_dev_tokens_prep_cursor = db_connection.cursor(prepared=True)
+    update_dev_tokens_prep_cursor.execute(update_new_dev_tokens_statement, dev_tokens_users)
     db_connection.commit()
     
     # NOW DO USER SUMMARY STATS
     user_summary_stats_insert_statement = (
         "insert into user_system_summary_stats "

Vulnerable code:     #print("dev_tokens_users: " + str(dev_tokens_users))

    ####################
    # TRIED DO UPDATE WITH PASSED LIST NONE OF THIS WORKED
    # HAD To build up the entire string
    #    update_new_dev_tokens_statement = (
    #        "update user_info set dev_token_first_seen = now() "
    #        "where dev_token_first_seen is null and "
    #        "username in (%s)"
    #        )
    #    sql_params = ",".join(dev_tokens_users)
    #    sql_params = (dev_tokens_users,)
    #    sql_params = ([str(dev_tokens_users)])
    #    cursor.execute(update_new_dev_tokens_statement, [sql_params])
    #    cursor.execute("update user_info set dev_token_first_seen = now() "
    #                   "where dev_token_first_seen is null and "
    #                   "username in (%s)" % ', '.join('?' * len(dev_tokens_users)), dev_tokens_users)
    #    update_new_dev_tokens_statement = (
    #        "update user_info set dev_token_first_seen = now() "
    #        "where dev_token_first_seen is null and "
    #        "username in (%s)" % ', '.join('?' * len(dev_tokens_users)), dev_tokens_users
    #        )
    #    cursor.execute("SELECT foo.y FROM foo WHERE foo.x in (%s)" % ', '.join('?' * len(s)), s)
    dev_tokens_string = "', '".join(dev_tokens_users)
        "username in ('" + dev_tokens_string + "')"
    cursor.execute(update_new_dev_tokens_statement)
Fixed code:         "username in (" + ("%s, " * (len(dev_tokens_users) - 1)) + "%s)"
#    print("update_new_dev_tokens_statement : " + update_new_dev_tokens_statement)
    update_dev_tokens_prep_cursor = db_connection.cursor(prepared=True)
    update_dev_tokens_prep_cursor.execute(update_new_dev_tokens_statement, dev_tokens_users)