Although this article talks about server prepared SQL statements for MySQL, it also applies to any sort of database that has parameterized queries available.
Parameterized Queries help prevent SQL injection and reduce the MySqlEscape or SQLEscape function pollution in your code. Why? Because parameterized queries prepare your SQL statements with $macrovars first (params) like an SQL template. It is similar to how HTML templates work with $macrovars embedded in them. The server prevents SQL injection since the macrovars are escaped by the SQL server (rather than you) before placing them into the fed SQL statement for the server to process.
(Instead of $macrovars you will use simple question marks in some database API's)
Parameterized Queries are Infamous
I didn't realize that most people are a bit clueless when it comes to databases. For example looking up the phrase "parameterized queries" on google for MySQL brings up almost nothing.. or just a bunch of mailing list posts that have no replies.
How can it be that people running serious websites using MySQL don't know about parameterized queries? How can it be that the community is not educated about these?
There appears to be a few different names for Parameterized Queries. I call them SQL Templates.. which is essentially what they are. Other people call them "prepared statements", "prepared SQL statements", "statements that have binded parameters", "prepared mysql queries", "prepared sql queries".
Anyway, here is a page below describing some of the ways to use mysql templates or parameterized mysql queries without rolling your own Format() style function which requires MysqlEscape.
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
If your database is so poor that it doesn't support parameterized queries, you might want to build your own format() style function that replaces $values in your query.. but it is very tricky to get the escaping perfectly right so that no injection ever occurs. As far as I know, mysql version 3 doesn't have parameterized queries and only later 4.1 versions of MySQL do.
Ask a programmer what a parameterized query is and many will wonder what planet you are from.
How to use the prepared mysql statements using MySQL api? Well Here are some of the ugly functions:
function mysql_stmt_init(mysql:PMYSQL):PMYSQL_STMT;
function mysql_stmt_prepare(stmt:PMYSQL_STMT; query:Pchar; length:dword):longint;
function mysql_stmt_execute(stmt:PMYSQL_STMT):longint;extdecl;
function mysql_stmt_param_count(stmt:PMYSQL_STMT):dword;
function mysql_stmt_bind_param(stmt:PMYSQL_STMT; bnd:PMYSQL_BIND):my_bool;
function mysql_stmt_bind_result(stmt:PMYSQL_STMT; bnd:PMYSQL_BIND):my_bool;
function mysql_stmt_close(stmt:PMYSQL_STMT):my_bool;
function mysql_stmt_reset(stmt:PMYSQL_STMT):my_bool;
I am working on making some wrappers for MySQL.
Full documentation on the wrappers should be available soon.
The mysql API interfaces are available here:
Hopefully the FCL-DB, ZeosLib, and PDO (Pascal Data Objects) packages also support parameterized queries. Please bring it up on the mailing lists if parameterized queries are unsupported in the tools you are using.
See also: http://www.codinghorror.com/blog/archives/000275.html
Subject: server-side prepared statements, prepared sql statements, paramaterized queries, parameterized queries, serverside prepared statements, prepared queries, prepared server-side queries, prepared server-side statements, parameterized sql statements, parameterized sql queries, binded parameters, bind parameters, sql binding parameters
|
