Using prepared statement is favorable in two regards: It is more efficient than direct execution of SQL code, since a statement that is used repeatedly needs to be parsed, analyzed, and, optimized only once. This can lead to a significant performance gain, depending on the complexity of the statement. Secondly, since no user input is used to create the SQL statement, but parameters are only passed as function arguments when the statement is executed, SQL injection is effectively prevented.
So here is a short example of how to use this from Lua using the luapgsql interface.
First, connect to a database:
require 'pgsql'
local conn = pgsql.connectdb('')
Using a prepared statement is a two step process. First, the statement needs to be prepared. This is done only once per connection using the prepare() function.
When preparing a statement, use $1, $2, $3 in the SQL code as placeholders for the actual data parameters, which will be supplied later, when the statement is being executed. To give PostgreSQL an idea which data types you will be using you have to supply "example" parameters to the prepare() function. The value of the parameter will be ignored, it will only be used to determine the data type. In the example below a prepared statement with name 'myselect' and one parameter, a string value, is created:
conn:prepare('myselect', 'select firstname from person where lastname = $1', '')
Now the prepared statement can be used as often as needed using the execPrepared() function, indicating the name of the prepared statement and supplying the actual data values:
res = conn:execPrepared('myselect', 'Miller')
Tracked: Oct 07, 17:29