A prepared statement is a feature in a database that executes a parameterized query. One of the main reasons to utilize a prepared statement is because they are resilient to SQL Injection Attacks. Because of this, we highly recommend you utilize Prepared Statements over Standard Statements, but Named Queries are the most secure. Especially in cases where the users can type values that will be included in the query.
Prepared Statements typically involve passing two pieces of information to the database:
Typical SQL insert queries look like the following:
INSERT INTO orders (account_id, product_name) VALUES (123, 'Bananas')
A Prepared Statement instead looks like the following. Note, that the placeholders do not require quotation marks even when a string will be passed in.
INSERT INTO orders (account_id, product_name) VALUES (?, ?)
Prepared Statements can be called from a script using specific functions. Typically, they contain "Prep" in the name such as system.db.runPrepQuery, or system.db.runPrepUpdate. When in doubt, take a look at the sub pages in the system.db section.
There are typically two required parameters with these functions: a string literal that represents the query, and a list of parameters.
query = "INSERT INTO orders (account_id, product_name) VALUES (?, ?)" args = [123, "Bananas"] system.db.runPrepUpdate(query, args)
Queries can be called as a Standard Statement (a statement that that isn't a Prepared Statement) by using the system.db.runQuery and system.db.runUpdateQuery functions. However, these are susceptible to SQL Injection attacks, and should be avoided where possible: especially when users have access to a keyboard and can directly type values that will be used in the query.
Calling a Standard Statement involves building the entire query as a single string, and passing the string on to our Standard Statement functions.
query = "INSERT INTO orders (account_id, product_name) VALUES (%i, '%s')" % (123, "Bananas") system.db.run(query)
If your database administrator has already configured Stored Procedures for you to use, then they can easily be called from a Python Script. Using Stored Procedures in a script typically involves two main steps:
# Create a SProcCall object, which will be used to configure parameters on the Stored Procedure, and then executed. myCall = system.db.createSProcCall("insert_new_order") # Register parameters on the SProcCall object. myCall.registerInParam(1, system.db.INTEGER, 123) myCall.registerInParam(2, system.db.VARCHAR, "Bananas") # Execute the Stored Procedure. system.db.execSProcCall(myCall)
Take a look at the SQL Stored Procedures page for more details.
A SQL Transaction can also be executed from a script. For the unfamiliar, a Transaction is a batch of statements that will be executed together, and either succeed or fail as a group. Note, that the statements executed in the Transaction are not visible by other connections in the database until you commit them.
Transactions typically involve several steps:
# 1) Begin the transaction. This returns a transaction identifier that we can use with other statements. transactionId = system.db.beginTransaction(timeout = 5000) # 2) Now we can execute statements. Because we want these to run as part of the transaction, we need to include our identifier. query = "INSERT INTO orders (account_id, product_name) VALUES (?, ?)" args = [123, "Bananas"] system.db.runPrepUpdate(query, args, tx = transactionId) # 3) We can continue to add statements, but in this case we'll commit them. We could instead rollback if there was an issue with our previous statement. system.db.commitTransactions(transactionId) # 4) We're done, so close the Transaction. system.db.closeTransaction(transactionId)