As if santization isn't enough, most people recommend using parameterized queries rather than standard string-based queries. To understand why, look at our original query:
$insertQuery = "INSERT INTO tweets(oid, body, latitude, longitude, day, hour, min) VALUES($oid,'$text',$lat,$lon,$day,$hour,$min)";
The problem with it is that it is all held in a string, and therefore any part of the string (for example one of the variables sent in) can end the string and start some new command. The solution to this is to hold the data chunks of the string (in this case the variables) separate from the command part of the string, and run the command, only pulling in the data when needed (and even better, to do some encoding of anything problematic in it at the same time). That way there's no chance of data running as commands. This is the idea behind parameterized queries. The query itself is hardwired with markers for the data, and the data is passed in when needed.
In PHP, you construct a parameterized query, thus:
$dbconn = pg_connect($pgsqlOptions);
$insertQuery = pg_prepare(
$dbconn, "my_query",
"INSERT INTO tweets(oid, body, latitude, longitude, day, hour, min) VALUES($1,'$2',$3,$4,$5,$6,$7)"
);
$result = pg_execute(
$dbconn, "my_query",
array($oid,$text,$lat,$lon,$day,$hour,$min)
);
With this in mind, here's a zip file containing the same files as before, but this time snuggled in some security stuff. Unzip them and check them out, then upload the files to the server, replacing the insecure versions, and try them out. Once you've done that, if you want to, have a go at seeing if you can build this interface, or something better, into your Leaflet maps.
While we call these files "secure", as we've said before, there's really no such thing. Have another look at Bill Karwin's talk, along with AMol NAik's SQL Injection in INSERT Query and Steve Friedl's SQL Injection Attacks by Example and the Bobby Tables site. If you are going to allow SQL-based access to your server, you need to be prepared, *really* prepared. Some good starting points are Thomas Mueller's SQL Injections: How Not To Get Stuck, which includes some server settings that help, along with OWASP's SQL Injection Prevention Cheat Sheet, Query Parameterization, and XSS Cheat Sheets. There are also a wide variety of Grey-Hat and Ethical Hacker books on the subject, should you wish to go down that defence route.