How to use the \'like\' wildcard effectively with prepared statements?

Utilizing "like" Wildcard in Prepared Statement

Prepared statements offer an effective way to execute SQL queries by preventing SQL injection attacks and enhancing performance. However, the integration of the "like" wildcard with prepared statements can be a bit tricky.

Consider the following query that searches for rows in the "analysis" table based on a keyword:

PreparedStatement pstmt = con.prepareStatement(
      "SELECT * FROM analysis WHERE notes like ?");
pstmt.setString(1, notes);
ResultSet rs = pstmt.executeQuery();

To use the "like" wildcard with this query, you need to include it within the value being bound to the prepared statement parameter. Simply adding "keyword%" to pstmt.setString(1, notes) won't suffice.

Instead, you can implement prefix, suffix, or global matching depending on your needs:

• Prefix Matching: Prefix matching searches for rows that start with the keyword. To achieve this with your query, use the following code:

notes = notes
    .replace("!", "!!")
    .replace("%", "!%")
    .replace("_", "!_")
    .replace("[", "![");
PreparedStatement pstmt = con.prepareStatement(
        "SELECT * FROM analysis WHERE notes LIKE ? ESCAPE '!'");
pstmt.setString(1, notes + "%");

• Suffix Matching: Suffix matching searches for rows that end with the keyword. To implement this, modify the code as follows:

pstmt.setString(1, "%" + notes);

• Global Matching: Global matching searches for rows that contain the keyword anywhere within the column value. To perform global matching, use the following code:

pstmt.setString(1, "%" + notes + "%");

Remember to escape special characters in the "notes" string using a forward slash () to prevent them from interfering with the "like" wildcard search.

The above is the detailed content of How to use the 'like' wildcard effectively with prepared statements?. For more information, please follow other related articles on the PHP Chinese website!