Правильно форматировать SQL запрос, если вставить в переменное число столбцов

голоса
2

Я использую psycopg2для взаимодействия с PostgreSQLбазой данных. У меня есть функция которой любое количество столбцов (от одного столбца для всех столбцов) в таблице может быть вставлено в. Мой вопрос: как бы один должным образом, динамически, построить этот запрос?

На данный момент я использую форматирование строк и конкатенацию , и я знаю , что это абсолютный худшим способ сделать это. Рассмотрим следующий код , в котором, в этом случае, мой неизвестное количество столбцов (т.е. ключи от dictфактически является 2):

dictOfUnknownLength = {'key1': 3, 'key2': 'myString'}

def createMyQuery(user_ids, dictOfUnknownLength):
    fields, values = list(), list()

    for key, val in dictOfUnknownLength.items():
        fields.append(key)
        values.append(val)

    fields = str(fields).replace('[', '(').replace(']', ')').replace(', )
    values = str(values).replace('[', '(').replace(']', ')')

    query = fINSERT INTO myTable {fields} VALUES {values} RETURNING someValue;

query = INSERT INTO myTable (key1, key2) VALUES (3, 'myString') RETURNING someValue;

Это обеспечивает правильно отформатированный запрос, но, конечно, склонной к инъекции SQL и тому подобное, и, как таковой, не является приемлемым способом достижения своей цели.

В других запросах я использую рекомендуемые методы построения запросов при обработке известного числа переменных ( %sи отдельный аргумента , .execute()содержащих переменные) , но я не уверен в том , как приспособить это , чтобы приспособить неизвестное число переменных без использования форматирования строк.

Как я могу изящно и безопасно построить запрос с неизвестным количеством указанных столбцов вставки?

Задан 27/11/2018 в 15:02
источник пользователем
На других языках...                            


1 ответов

голоса
1

Для того, чтобы добавить в ваши заботы, существующая методика использования .replace()склонна к случаям краев , где поле или значения содержат [, ]или '. Они не заменяются независимо от того , что и может испортить ваш запрос.

Вы всегда можете использовать , .join()чтобы присоединиться к переменному количеству значений в списке. Для того, чтобы пополнить его, форматировать запрос соответствующим образом с %sпосле того, как VALUESи передать ваши аргументы в .execute().

Примечание: Вы можете также рассмотреть случай, когда число полей не равны значениям числа.

## for completeness
import psycopg2
conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()

dictOfUnknownLength = {'key1': 3, 'key2': 'myString'}

def createMyQuery(user_ids, dictOfUnknownLength):

    ## this may be quicker
    fields, values = list(dictOfUnknownLength.keys()), list(dictOfUnknownLength.values())

    if len(fields) != len(values):
        ## raise an error? sql won't work in this case anyway...
        pass

    fieldsParam = ','.join(fields)  ## key1, key2 ## directly stringify it
    valuesParam = ','.join(['%s']*len(values)))  ## %s, %s

    ## INSERT ... (key1, key2) VALUES (%s, %s) ...
    query = 'INSERT INTO myTable ({}) VALUES ({}) RETURNING someValue;'.format(fieldsParam, valuesParam)

    ## .execute('INSERT ... (key1, key2) VALUES (%s, %s) ...', [3, 'myString'])
    cur.execute(query, values)  ## anti-sql-injection
Ответил 27/11/2018 в 15:11
источник пользователем

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more