Increase security of DB INSERT

Question

Currently, I have a PostGIS DB, and I have the basics working. However, I am inserting directly into the database using pg_query. I have been recommended to use pg_query_params to help prevent against SQL injections, but am unsure how to implement this. Below is a cut-down example of my current insert statement for a site location. How would I, for example, utilise pg_query_params with this example? I know I will have to implement further security, but it is a starting point.

EDIT: I was going to use the drupal form API but it gave me headaches. I realize that would do a lot of this stuff automatically.

$sql = "INSERT INTO sites_tbl (river_id ,sitename ,the_geom) VALUES ('$_POST[river_id]','$_POST[sitename]',st_geomfromtext('POINT($geomstring)',27700))";
$result = pg_query($sql);

Answer 1

Because you are using strings rather than parameters, your example is vulnerable to SQL injection. It's best to avoid pg_ functions. In your case there are two things you need to take into account:

• Learn the Drupal API (considering you are using Drupal this would be the best for code consistency

or

• Use stored procedures
• Use a library like PDO or pg_query_params which takes care of parameterized queries

Normally you use stored procedures in addition to PDO, unfortunately sometimes this is not manageable because you have too much code. My advice is to use as much stored procedures as possible.