Feature: Prepared statements

Problem Statement

Hazaar now uses prepared statements for all database queries executed through the QueryBuilder. However, these prepared statements are currently only used internally within the DBI PDO driver class for SQL injection protection via parameter binding. There is no way for developers to retrieve and reuse prepared statements when executing multiple queries, such as batch updates or repeated selects. This results in redundant query preparation, which impacts performance and efficiency.

Who will benefit?

• Developers executing multiple queries of the same structure.
• Applications that require high-performance database operations with reduced overhead.
• Any use case where repeated queries with different parameters are necessary.

Benefits and risks

Benefits

• Reduces the overhead of preparing the same SQL query multiple times.
• Improves execution speed by reusing prepared statements.
• Enhances database efficiency, especially for batch operations.

Risks

• Exposing prepared statements directly may introduce misuse if not properly managed.
• Additional complexity in managing statement lifecycle and bindings.

Proposed solution

Introduce a mechanism to retrieve and reuse prepared statements in the DBI component:

• Modify the QueryBuilder to allow retrieval of a prepared statement before execution.
• Provide an interface for executing a prepared statement with new parameters.
• Ensure proper resource management and statement cleanup to prevent memory leaks.

Priority/Severity

• High (This will bring a huge increase in performance/productivity/usability/legislative cover)
• Medium (This will bring a good increase in performance/productivity/usability)
• Low (anything else e.g., trivial, minor improvements)