Home / Questions / 30393
Accepted
TwoDent
Asked: 2020-10-29 06:19:59 +0800 CST 2020-10-29 06:19:59 +0800 CST

Create query with parameterized LIKE? (JAVA/UcanAccess)

  • 772

Good! I come here with a small question: How exactly could you write a query using a Like and have it be parameterized? I am using UcanAccess with JAVA.

I have a window in my program that requires the use of the Like. I tried first writing it "normal" like this, but it wasn't what I was looking for:

SELECT Diagnostico FROM Historial WHERE CedulaP LIKE ? ORDER BY Fecha ASC

I needed to insert the percentage character (%) for it to work as I wanted... so what I did is write the following:

SELECT Diagnostico FROM Historial WHERE CedulaP LIKE CONCAT('%',?,'%') ORDER BY Fecha ASC

The query works fine, but I don't know why I had doubts as to whether doing it this way is safe or not (because of SQL Injection ). This code I wrote is where I prepare the PreparedStatementto execute the query:

public ResultSet SelectQuery(String[] values, String query) throws SQLException {
    PreparedStatement stmt = conn.prepareStatement(query);
    ResultSet rs;
    for(int i = 0; i<values.length; i++){
        stmt.setString(i + 1, values[i]);
    }
    rs = stmt.executeQuery();
    return rs;
}

Is the query safe the way I wrote it?

java

1 Answers

  1. Best Answer

    If it's safe, since you can't do a subquery inside a string (which is what you did) because the entire statement would be escaped, you don't have to worry about SQL injection, the problem would be if you did the following.

    String variable ="(DELETE FROM Historial)as Hecho";
String query = "SELECT Diagnostico FROM Historial WHERE CedulaP LIKE CONCAT('%',"+varible+",'%') ORDER BY Fecha ASC";

    With the preparedStatement as you have it, simply by concatenating it would happen like this

    String query = "SELECT Diagnostico FROM Historial WHERE CedulaP LIKE CONCAT('%','(DELETE FROM Historial)as Hecho','%') ORDER BY Fecha ASC";

    Something like that.

    Greetings.

    • 1