Feature: QueryBuilder prepared statements

Problem Statement

The Hazaar DBI query builder currently does not utilize PHP's prepared statement queries, which can lead to potential security risks (e.g., SQL injection) and performance inefficiencies due to repeated query parsing. Implementing prepared statements will improve security and efficiency in query execution.

Who will benefit?

• Developers using Hazaar DBI for database interactions.
• Applications that require secure and efficient database queries.
• Systems handling user input, reducing the risk of SQL injection.

Benefits and risks

Benefits

• Security: Protects against SQL injection by properly binding query parameters.
• Performance: Reduces query parsing overhead by allowing query reuse.
• Maintainability: Simplifies query building by enforcing parameterized inputs.

Risks

• Backward Compatibility: Existing queries may require refactoring.
• Implementation Complexity: The query builder needs to be redesigned to support prepared statements properly.

Proposed Solution

1. Modify the Hazaar DBI query builder to generate prepared statements instead of raw SQL queries.
2. Implement parameter binding to replace direct variable interpolation.
3. Ensure compatibility with different database drivers.
4. Provide a migration guide for developers to update existing queries.
5. Implement testing to verify query execution and security improvements.

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)