CODEWITHSUNDEEP
javasqljdbcbinding
Pablo
Pablo

Reputation: 1081

Cannot escape parameters inside single quotes on a JDBC PreparedStatement

I want to make a query like this (note it contains some postgis functions) via JDBC:

SELECT id, name, ST_Y(location::geometry) as latitude, ST_X(location::geometry) as longitude, category_group, category 
FROM pois 
WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(-5.8340534 43.3581991)'), 1000000);

so I parametrized the query to:

SELECT id, name, ST_Y(location::geometry) as latitude, ST_X(location::geometry) as longitude, category_group, category 
FROM pois 
WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(? ?)'), ?);

The problem is that when executing the preparedStament it can't find the first 2 parameters, because they are inside single quotes, so it gives an error.

I also tried to escape the single quotes with \' without success:

SELECT id, name, ST_Y(location::geometry) as latitude, ST_X(location::geometry) as longitude, category_group, category 
FROM pois 
WHERE ST_DWithin(location, ST_GeographyFromText(\'SRID=4326;POINT(? ?)\'), ?);

For now, as a workaround, I'm not binding the parameters, just appending the query string with them, but that's susceptible to SQL Injection, so I just want to know if there is any way to escape that sentence to make parameter binding work.

Upvotes: 5

Views: 2432

Answers (2)

Abhishek
Abhishek

Reputation: 1

As a naive answer....

You can assign the parameters before the query and later concatenate it with the Query ex:

Scanner scan = new Scanner(System.in); //don't forget to import java.util package
String par1 = scan.nextLine();
String par2 = scan.nextLine();

Later

String query = "SELECT id, name, ST_Y(location::geometry) as latitude, " + 
    "ST_X(location::geometry) as longitude, category_group, category " +
    "FROM pois " +
    "WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT("+par1+" "+par2+")'), ?)";

now pass this query to the Query Creation Statement

Upvotes: 0

Pablo
Pablo

Reputation: 1081

Asking myself, I'm using string concat:

"SELECT id, name, ST_Y(location::geometry) as latitude, ST_X(location::geometry) as longitude, category_group, category 
FROM pois 
WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(' || ? || ' ' || ? || ')'), ?);"

Upvotes: 6

Related Questions