Impyla Вставка SQL из Flask: синтаксическая ошибка (привязка идентификатора)

Недавно я настроил конечную точку Flask POST для записи данных в Impala DB через модуль Impyla.

Конверт: Python 3.6.5 на CentOS.

Версия Impala: версия Impalad 2.6.0-cdh5.8.0

api.py:

from flask import Flask, request, abort, Response
from flask_cors import CORS
import json
from impala.dbapi import connect
import sys
import re
from datetime import datetime


app = application = Flask(__name__)
CORS(app)


conn = connect(host='datanode2', port=21050,
            user='user', database='testdb')


@app.route("/api/endpoint", methods=['POST'])
def post_data():
    # if not request.json:
    #     abort(400)

    params = request.get_json(force=True)  # getting request data
    print(">>>>>> ", params, flush=True)

    params['log_time'] = datetime.now().strftime("%Y-%m-%d %H-%M-%S")
    # params['page_url'] = re.sub(
    #     '[^a-zA-Z0-9-_*.]', '', re.sub(':', '_', params['page_url']))

    try:
        cursor = conn.cursor()

        sql = "INSERT INTO table ( page_title, page_url, log_time, machine, clicks, id ) VALUES (%s, %s, %s, %s, %s, %s)"
        values = (params['page_title'], params['page_url'], params['log_time'],
                params['machine'], params['clicks'], params['id'])
        print(">>>>>> " + sql % values, file=sys.stderr, flush=True)

        cursor.execute(sql, values)

        print(
            f">>>>>> Data Written Successfully", file=sys.stderr, flush=True)
        return Response(json.dumps({'success': True}), 201, mimetype="application/json")
    except Exception as e:
        print(e, file=sys.stderr, flush=True)
        return Response(json.dumps({'success': False}), 400, mimetype="application/json")


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5008, debug=True)

req.py:

import requests as r

url = "http://123.234.345.456:30001/"
# url =  "https://stackru.com/questions/ask"

res = r.post('http://localhost:5008/api/endpoint', 
            json={             
                "page_title": "Home",   
                "page_url": url,
                "machine": "Mac OS",
                "clicks": 16,
                "id": "60cd1d79-eda7-44c2-a4ec-ffdd5d6ac3db"         
            }
        )

if res.ok:
    print(res.json())
else:
    print('Error!')

Я запустил колбу API с python api.py затем проверить это с python req.py,

Флэш-сервер выдает эту ошибку:

>>>>>>  {'page_title': 'Home', 'page_url': 'http://123.234.345.456:30001/', 'machine': 'Mac OS', 'clicks': 16, 'id': '60cd1d79-eda7-44c2-a4ec-ffdd5d6ac3db'}
>>>>>> INSERT INTO table ( page_title, page_url, log_time, machine, clicks, id ) VALUES (Home, http://123.234.345.456:30001/, 2018-12-12 16-14-04, Mac OS, 16, 60cd1d79-eda7-44c2-a4ec-ffdd5d6ac3db)
AnalysisException: Syntax error in line 1:
..., 'http://123.234.345.456'2018-12-12 16-14-04'0001/', ...
                         ^
Encountered: INTEGER LITERAL
Expected: AND, AS, ASC, BETWEEN, CROSS, DESC, DIV, ELSE, END, FOLLOWING, FROM, FULL, GROUP, HAVING, ILIKE, IN, INNER, IREGEXP, IS, JOIN, LEFT, LIKE, LIMIT, NOT, NULLS, OFFSET, OR, ORDER, PRECEDING, RANGE, REGEXP, RIGHT, RLIKE, ROWS, THEN, UNION, WHEN, WHERE, COMMA, IDENTIFIER

CAUSED BY: Exception: Syntax error

Эта ошибка немного раздражает:

  1. Я попытался напрямую вставить команду sql в оболочку impala, и она работает.

  2. Когда page_url является единственным параметром, он тоже работает нормально.

Так что же это за условные экранирующие символы? Мне удалось обойти эту проблему, настроив URL с помощью регулярного выражения (Uncomment Line 27 - 28). Но это действительно раздражает, я не хочу очищать свои данные из-за этого.

Когда я проверяю испытания других людей, считается, что добавление пары цитат к каждой вставке значений будет работать. Тем не менее, как я могу сделать это при использовании форматирования строки, и это должно произойти до cursor.execute(sql, values)?

Источник

2 ответа

Решение

После некоторой борьбы и большой помощи со стороны @Scratch'N'Purr и @msafiullah в проблеме подстановки параметров #317 мне удалось заставить ее работать. Это довольно сложно, поэтому я выложу полный код для документации:

Причина ошибки: проблема с удалением двоеточия через API Impyla.

Решение: использовать настраиваемую функцию экранирования для обработки данных и использовать SQL-инъекцию (способ форматирования строк в Python для замены параметров) вместо стандартного API-интерфейса Python DB, например cursor.execute(sql, values),

api.py:

from flask import Flask, request, abort, Response
from flask_cors import CORS
import json
from impala.dbapi import connect
from impala.util import _escape
import sys    
from datetime import datetime
import six

app = application = Flask(__name__)
CORS(app)


conn = connect(host='datanode2', port=21050,
            user='user', database='testdb')


def parameterize(value): # by msafiullah
    if value is None:
        return "NULL"
    elif isinstance(value, six.string_types):
        return "'" + _escape(value) + "'"
    else:
        return str(value)


@app.route("/api/endpoint", methods=['POST'])
def post_data():
    if not request.json:
        abort(400)

    params = request.get_json(force=True)  # getting request data
    print(">>>>>> ", params, flush=True)

    params['log_time'] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    try:
        cursor = conn.cursor()

        sql = 'INSERT INTO table ( page_title, page_url, log_time, machine, clicks, id ) VALUES ( CAST({} AS VARCHAR(64)), {}, {}, CAST({} AS VARCHAR(32)) , {}, CAST({} AS VARCHAR(32)))'\
                .format(parameterize(params['page_title']), parameterize(params['page_url']), parameterize(params['log_time']), parameterize(params['machine']), params['clicks'], parameterize(params['id']))
        print(">>>>>> " + sql, file=sys.stderr, flush=True)

        cursor.execute(sql)

        print(
            f">>>>>> Data Written Successfully", file=sys.stderr, flush=True)
        return Response(json.dumps({'success': True}), 201, mimetype="application/json")
    except Exception as e:
        print(e, file=sys.stderr, flush=True)
        return Response(json.dumps({'success': False}), 400, mimetype="application/json")


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5008, debug=True)

req.py такой же, как вопрос.

table схема:

CREATE TABLE if not exists table (
    id VARCHAR(36),
    machine VARCHAR(32),
    clicks INT,
    page_title VARCHAR(64),
    page_url STRING,
    log_time TIMESTAMP
);

Вывод сервера Flask:

>>>>>>  {'page_title': 'Home', 'page_url': 'http://123.234.345.456:30001/', 'machine': 'Mac OS', 'clicks': 16, 'id': '60cd1d79-eda7-44c2-a4ec-ffdd5d6ac3db'}
>>>>>> INSERT INTO table ( page_title, page_url, log_time, machine, clicks, id ) VALUES ( CAST('Home' AS VARCHAR(64)), 'http://123.234.345.456:30001/', '2018-12-14 17:27:29', CAST('Mac OS' AS VARCHAR(32)) , 16, CAST('60cd1d79-eda7-44c2-a4ec-ffdd5d6ac3db' AS VARCHAR(32)))
>>>>>> Data Written Successfully
127.0.0.1 - - [14/Dec/2018 17:27:29] "POST /api/endpoint HTTP/1.1" 201 -

Внутри импала-ракушка, select * from table дам:

+----------------------------------+--------+--------------+------------+----------------------------------------------------------------------+---------------------+
| id                               | machine | clicks      | page_title | page_url                                                             | log_time            |
+----------------------------------+--------+--------------+------------+----------------------------------------------------------------------+---------------------+
| 60cd1d79-eda7-44c2-a4ec-ffdd5d6a | Mac OS | 16           | Home       | http://123.234.345.456:30001/                                        | 2018-12-14 17:27:29 |
+----------------------------------+--------+--------------+------------+----------------------------------------------------------------------+---------------------+

В основном только цифры (например, INT типа) не нужно проходить через parameterize() процесс очистки / побега. Другие типы, такие как VARCHAR, CHAR, STRING, TIMESTAMP (из-за двоеточий) следует избегать для безопасной вставки через API Impyla.

Источник

Impyla или другие основанные на impala библиотеки Python не поддерживают параметризованные запросы, как это делают традиционные базы данных SQL. Единственное решение, с которым я столкнулся, заключалось в том, чтобы заключить значения вставки в кавычки, если значения определены как строка / метка времени.

Вы упоминаете, как это сделать при использовании форматирования строки перед выполнением запроса? Просто, просто примените форматирование строки, а затем вставьте форматированное значение.

В вашем примере предположим, что ваша таблица имеет следующие определения типов:

CREATE TABLE table (
    page_title VARCHAR(64),
    page_url STRING,
    log_time TIMESTAMP,
    machine VARCHAR(64),
    clicks INT,
    id CHAR(36)
)

Тогда ваш оператор вставки будет:

sql = "INSERT INTO table ( page_title, page_url, log_time, machine, clicks, id ) VALUES ('%s', '%s', '%s', '%s', %s, '%s')"  # note the single quotes around the string/timestamp types

Сейчас с log_time это метка времени, вам придется отформатировать datetime.now() к yyyy-MM-dd HH:mm:ss формат.

params['log_time'] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

Если бы вы определили log_time как STRING вместо TIMESTAMP, тогда ваш формат %Y-%m-%d %H-%M-%S должно сработать.

Наконец, выполните:

values = (params['page_title'], params['page_url'], params['log_time'],
          params['machine'], params['clicks'], params['id'])
cursor.execute(sql, values)

Обратите внимание, что этот метод работает только при работе с базовыми типами данных, такими как числа или строки. Все сложные объекты, такие как массивы или структуры, работать не будут.

Источник
Другие вопросы по тегам