How to parameterize the frappe.db.sql call?

Thanh_Dao · March 6, 2021, 12:51am

Hi guys,

I need to use some complex syntax hence the basic Database API is not usable, so i need to use frappe.db.sql to execute a raw sql statement ?.

But I can’t find how to parameterize the SQL statement in the official documentation. I know i can use string interpolation, but it’s not good due to security vulnerabilities (SQL injection ?)

Thank you alot

revant_one · March 6, 2021, 11:55am

use frappe.db.sql(query, params)

first param is query as string,
second param is values tuple.

github.com

frappe/frappe/blob/16d87aa78664f8c2c7bfbb5dcfeed526ab62f19d/frappe/database/database.py#L88


      
          	def use(self, db_name):
          		"""`USE` db_name."""
          		self._conn.select_db(db_name)
          
          
	def get_connection(self):
          		pass
          
          
	def get_database_size(self):
          		pass
          
          
	def sql(self, query, values=(), as_dict = 0, as_list = 0, formatted = 0,
          		debug=0, ignore_ddl=0, as_utf8=0, auto_commit=0, update=None, explain=False):
          		"""Execute a SQL query and fetch all rows.
          
          
		:param query: SQL query.
          		:param values: List / dict of values to be escaped and substituted in the query.
          		:param as_dict: Return as a dictionary.
          		:param as_list: Always return as a list.
          		:param formatted: Format values like date etc.
          		:param debug: Print query and `EXPLAIN` in debug log.
          		:param ignore_ddl: Catch exception if table, column missing.