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

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 :)

Issue 113 has been merged into this issue.

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