2

I'm working with Postgres 8.3/PostGIS which is storing some polygons. I'm writing a javascript function which will take in a parameter of a watershed id from another function. The javascript will build up a sql statement and then send that along to a jsp script which will send that to the database and return some geojson which will then be output into Openlayers. The problem is that the geojson is not being returned because I think I have a problem with my select statement and some type (integer/text) problem. The table holding the polygon geometry contains a column set as varchar(50) but it contains some id's which have only numbers, and some id's which contain numbers and string characters. The function attempts to return a geojson from the database of a polygon of a given id. I've left off the last end of the function because I think my problem is somewhere in the building of the sql statement.

function displaySelectedShed(shedId) {
    var json_url = "04-sql-to-json.jsp?sql=";
    var sql_geom = "st_transform(ST_Force_2D(the_geom),900913)";
    var sql = "select st_asgeojson(" + sql_geom + "), id ";
        sql += "from medford.subshed2 ";
        sql += "where subshed2.id = " + shedId +"";
        json_url += encodeURIComponent(sql);
 }

The select statement above looks like this:

select st_asgeojson(st_transform(ST_Force_2D(the_geom),900913)), id from medford.subshed2 where subshed2.id = 01616500

and I can test this by putting this:

http://localhost:8080/spatialdbtips/04-sql-to-json.jsp?sql=select%20st_asgeojson%28st_transform%28ST_Force_2D%28the_geom%29%2C900913%29%29%2C%20id%20from%20medford.subshed2%20where%20subshed2.id%20%3D%2001616500

into a browser window. But the error I get is this:

Error: operator does not exist: character varying = integer

I know this has probably something to do with the fact that my shed id is a number when inserted as a variable into the sql statement, but I've tried all sorts of cast combinations to get it to be a string, since the values in the database are stored as such, but I can't get them to work. I've tried both the CAST as well as the :: such as

sql += "where subshed2.id = " + shedId +"::text";

but I can't get it to work. Does anyone have any suggestions to get my watershed id numbers (the shedId variable) to act like string in the sql where clause, and how to build it up in a way I've shown in javascript?

  • I'm also having the problem that when my incoming shedId is this: PU2_3900_3750 the browser returns this in the error: pu2_3900_3750. (It doesn't see the capital letters, hence the comparison fails). Maybe this is related to my question above, or at least maybe an answer will solve both problems. – Jan Jul 10 '13 at 17:34
  • 1
    Not sure why you are writing SQL statements in JavaScript. This seems ripe for SQL injection. I would suggest building the SQL statements in server-side code. – blah238 Jul 10 '13 at 23:56
  • If he builds queries as string literals and gets sql_geom and shedId from the client he'll be vulnerable to SQL injection either way. Bind variables are the best way to deal with that problem. – Russell at ISC Jul 11 '13 at 13:14
2

An easy way to treat shedId as text will be

sql += "where subshed2.id = '" + shedId +"'";

simply putting single quotes around it. Without it PostGRESql would treat numbers as numbers and expect character data to be an identifier.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.