Secure Data Access with Prepared Statements in Go

Prepared statements are a crucial technique for preventing SQL injection vulnerabilities and improving database query performance. This challenge will guide you in implementing prepared statements in Go to securely and efficiently interact with a database. You'll learn how to define a query once and then execute it multiple times with different parameters, safeguarding your application against malicious input.

Problem Description

You are tasked with creating a Go program that interacts with a database (assume a database connection is already established and available as db *sql.DB). The program should allow users to search for customers by their name. The search should be implemented using a prepared statement to prevent SQL injection.

Specifically, your program should:

1. Define a prepared statement: Create a prepared statement for the following SQL query: SELECT id, name, email FROM customers WHERE name LIKE ?. The ? represents a placeholder for the customer's name.
2. Execute the prepared statement: Take a customer name as input from the user (e.g., "John%"). Use this input to execute the prepared statement, substituting the placeholder with the provided name.
3. Retrieve and display results: Fetch the results from the database and print the id, name, and email of each matching customer. If no customers are found, print a message indicating that no results were found.
4. Handle errors: Properly handle any errors that occur during the prepared statement definition or execution. Print informative error messages to the console.

Expected Behavior:

The program should take a customer name as input, execute the prepared statement with that name, and display the details of any matching customers. It should gracefully handle cases where no customers are found or if there are errors during database interaction. The use of prepared statements is mandatory to prevent SQL injection.

Examples

Example 1:

Input: John%
Output:
id: 1, name: John Doe, email: john.doe@example.com
id: 3, name: John Smith, email: john.smith@example.com

Explanation: The program searches for customers whose name starts with "John". Two customers match the criteria, and their details are displayed.

Example 2:

Input: Jane%
Output:
No customers found.

Explanation: No customers have a name starting with "Jane". The program correctly indicates that no results were found.

Example 3: (Edge Case - Empty Input)

Input: ""
Output:
No customers found.

Explanation: An empty search term should return no results.

Constraints

• The database connection db *sql.DB is assumed to be already established and valid. You do not need to implement the database connection logic.
• The customers table exists and has columns id (INTEGER), name (TEXT), and email (TEXT).
• The input customer name will be a string.
• The program should handle potential errors during database operations gracefully.
• The solution must use prepared statements to prevent SQL injection. Failure to do so will result in a failed submission.

Notes

• Use the sql.Prepare() function to define the prepared statement.
• Use the sql.Query() function to execute the prepared statement with parameters.
• Use rows.Scan() to retrieve the results from the database.
• Remember to close the rows object after you are finished with it.
• Consider using defer rows.Close() to ensure the rows are closed even if errors occur.
• The % wildcard character is used in the LIKE clause to perform partial string matching. The input string should already include the wildcard.