Start learning free

LESSON 158 OF 173 · BACKEND: BACKEND WITH AI

The AI Pasted Text into SQL in Java

Practice

An AI assistant wrote a product search for this shop: /products/search?q=te finds every product whose name contains te. It built the SQL by joining strings with +:

String sql = "SELECT name, price FROM products WHERE name LIKE '%" + q + "%'";
List<Map<String, Object>> rows = db.queryForList(sql);

The + pastes the visitor's text into the SQL itself. A quote in that text ends the SQL string early, and the rest runs as SQL: Ana's breaks the query, and x' UNION SELECT name, email FROM customers -- answers every customer's name and email, as if they were products. This is SQL injection.

The fix is a placeholder: write ? in the SQL and pass the value after it, separated by a comma. Here it is on another table:

List<Map<String, Object>> rows = db.queryForList("SELECT name FROM customers WHERE city = ?", city);

The SQL text stays the same whatever the visitor types, and the database only compares the value, quotes and all. Your code must give these answers:

GET /products/search?q=te     →  200  [{"name": "Notebook", "price": 7}, {"name": "Teapot", "price": 22}]
GET /products/search?q=MUG    →  200  [{"name": "Mug", "price": 12}]
GET /products/search?q=Ana's  →  200  []
GET /products/search?q=x' UNION SELECT name, email FROM customers --  →  200  []
GET /products/search          →  200  [{"name": "Mug", "price": 12}, {"name": "Notebook", "price": 7}, … all 8 products]

Your task: rewrite searchProducts. q arrives as its parameter ("" when it is missing). Build the pattern "%" + q + "%" as a Java string, run the query with LIKE ? and the pattern as its value, and return the rows. Keep the same SELECT name, price FROM products as the old code and do not add ORDER BY. The rows must come back in the order they are stored in the table, and that order is not alphabetical.


Assignment
Follow the Coding Tutorial to rewrite searchProducts with a placeholder.


Hint
The % signs go into the Java string pattern, not into the SQL: the SQL only says LIKE ?. Pass pattern after the query, separated by a comma. Spring sends the list of rows as JSON.


STEP 1 OF 3
    Ctrl + Enter to check

    Ai Sql Injection in Java

    Keep going with Java1,600+ step-by-step lessons, from your first line of code to coding interviews. Free to start.
    Start learning free