Database (mysql) prepared statement interface

[GoogleCodeExporter]

Hi,

Taking user input, using with some string manipulation and then feeding that 
into DB is very dangerous. Even if you try to use string escaping.
This is a point that will be hammered into developers at every opportunity 
during a Web Security course.

The best practice given is always to use bounded variables/parameters (aka 
prepared statements). These also run much faster on most DBMS.

The work flow that one would expect from a DB API is as follows:

1.

var db = new DB_handle();
db.connect()

db is something that you can keep, even across connections.

2.

var query = db.query('SELECT * FROM table WHERE firstname = ? AND lastname = 
?');

'?' is at least MySQLs syntax. Query can be reused with different parameters 
(even across http connections); for queries that are use often this caching 
will give a speed boost. 

var result1 = query.execute('Foo', 'Bar');
var result2 = query.execute(userinput1, userinput2);

3.

Use iterators/operators on results.

The point here is, even if userinput1 = '"" Or 1=1 ;--';
it will _not_ be interpreted as SQL but rather as a string. 
So even names such as Bobby Tables' (http://xkcd.com/327/) are safe and can be 
searched for. Without the need to escape, which is not 100% reliable anyway.

Regards,

Thorben

Original issue reported on code.google.com by tj.treve...@gmail.com on 20 Jan 2011 at 10:00

[GoogleCodeExporter]

Yes, I am fully aware of all advantages of prepared statements. The only 
problem is that MySQL's Prepared statements API is quite complex and large, so 
I did not have (so far) enough time to implement it. Adding to my TODO list :)

Original comment by ondrej.zara on 20 Jan 2011 at 11:46

• Changed state: Accepted

[GoogleCodeExporter]

Issue 113 has been merged into this issue.

Original comment by ondrej.zara on 7 May 2012 at 6:04

[GoogleCodeExporter]

http://code.google.com/p/teajs/issues/detail?id=3

Original comment by ondrej.zara on 20 Jul 2012 at 7:47