How to pass dynamic value in SQL-LIMIT?

Question

Having error, while passing dynamic value in the SQL-LIMIT.

CODE

import mysql.connector
from flask import (Flask, jsonify, request)
from waitress import serve

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="deikho_app"
)

app = Flask(__name__)    

@app.route('/')
def home():
    return 'Flask Test'


@app.route('/api/foo/', methods=['GET'])
def foo():
    cursor = mydb.cursor(buffered=True)

    videoId = request.args.get('videoId')
    recordLimit = request.args.get('recordLimit')

    if recordLimit:
        query = """ SELECT * FROM cb_user_levels_permissions LIMIT %s """
        cursor.execute(query, (recordLimit,))
    else:
        query = """ SELECT * FROM cb_user_levels_permissions """
        cursor.execute(query)

    queryResult = cursor.fetchall()
    return jsonify(queryResult)

if __name__ == '__main__':
    serve(app, host='0.0.0.0', port=5000)

CODE EXPLANATION

Modules imported, connection with the database is created, and default URL is made.
Then in /api/foo/ part, else condition is working fine. But in the if condition I am having an error while passing dynamic value from URL to the query.
URL: http://127.0.0.1:5000/api/foo/?recordLimit=1

ERROR

ERROR:flaskTest:Exception on /api/foo/ [GET]
Traceback (most recent call last):
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\flask\app.py", line 2070, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\flask\app.py", line 1515, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\flask\app.py", line 1513, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\flask\app.py", line 1499, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "E:\flaskTest.py", line 46, in foo
    cursor.execute(query, (recordLimit,))
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''1'' at line 1

You can't parameterize LIMIT; string concat an integer on. If it's coming from the user, parse it to int first and concat the int on. Do not concat on a string from the user — Caius Jard, Jun 25 '21 at 06:52
Yes you absolutely _can_ parameterize LIMIT, unless the restriction lies purely in MySQL? — roganjosh, Jun 25 '21 at 07:02
The issue is that you passed a string for your limit, not an int — roganjosh, Jun 25 '21 at 07:05
My bad, I assumed it was a "pre-5.6 cannot parameterize limit" problem. See [here](https://stackoverflow.com/questions/2875238/passing-limit-as-parameters-to-mysql-sproc) — Caius Jard, Jun 25 '21 at 08:34

How to pass dynamic value in SQL-LIMIT?

0 Answers0