Using SQL within C code and the API will involve parameterized SQL—the way to include data placeholders in an SQL statement. These are the two types of parameterized binding: named and positional. See Figure 10 for more details on how these types of parameterized binding are used. The first statement is positional where its position is located or marked by a question mark, and these positions are based on the number of columns.
The real variable names setup in the programmable language, such as C or Java, as shown in the second insert statement in Figure 10, outlines the named parameters that use a colon as a prefix to indicate it on an SQL statement. By default, NULL is used as a default value if there is no value for it to be bound to.
Once a statement is bound, you can call on it again more than once without wasting the performance or time to recompile it again.
The whole idea of using parameterized SQL is to reuse the same code with different parameters without recompiling. It saves on resources and time, and improves efficiency. This allows the existing code to be reused several times if the design allows it, to save on more code and improve efficiency. If you use quotes or characters for plurals as an example, SQLite, by default, will escape the characters and insert the right data and convert it properly.
It also stops SQL injections, SQL penetrations, and easy syntax issues or errors. The SQL injection to a company is a security vulnerability, which allows a hacker to trick the system into adding or modifying data where access is not granted. On a browser where the address of a website is seen, the data input is sometimes added without any encryption, or no data checking is carried out at the backend or frontend to allow penetration. SQL injections, as illustrated and explained in Figure 12, show that an open piece of code that relies on an input using a %s string, can be regarded as opened, and can impact the data in the database:
Figure 10: Using parameterized SQL
The following snippet shows how a statement can be compiled using one set of parameters; using the function _reset() method will allow the same compiled SQL code to be used again with different parameters:
# example of using reset - START
db1= open('property.db')
sql_statement= db1.prepare('insert into property_info(id,property_id,desc) values(:id,:pr_id,:desc)')
sql_statement.bind('id','100')
sql_statement.bind('property_id','1')
sql_statement.bind('desc','this is a test')
sql_statement.step()
# Reuse existing compiled parameters
sql_statement.reset()
sql_statement.bind('id','200')
sql_statement.bind('property_id','2')
sql_statement.bind('desc','this is a test again')
# End
statement_sql.finalize()
db1.close()