9 ms·
> PDO is awesome but requires deeper level of understanding (for a beginner), which may increase the frustration and may end up returning back to the mysql_quer
by sarciszewski 11y ago
> PDO is awesome but requires deeper level of understanding (for a beginner), which may increase the frustration and may end up returning back to the mysql_query and co.
Yeah, that's basically why I wrote EasyDB. https://github.com/paragonie/easydb https://github.com/paragonie/easydb
$rows = $db->run('SELECT * FROM comments WHERE blogpostid = ? ORDER BY created ASC', $_GET['blogpostid']);
foreach ($rows as $row) {
// etc
}
Teach people to do things this way rather than concatenate strings, say goodbye to SQL injection vulnerabilities.
- deleted 11y ago[deleted]
- bpicolo 11y agoYep. PDO still leaves room for string concatenation issues. Unfortunately, so does Doctrine.
- Iridiumkoivu 11y agoWhat do you think of Propel?
- sarciszewski 11y agoPeople who want to do string concatenation will continue to do so no matter what we tell them. At some point, we have to educate users. Giving them an easy-to-use alternative is a step in the right direction.
- mikeash 11y agoWhy not have the obvious API call for making a query only accept constant strings? You can still have an escape hatch for the rare cases where an expert needs to do something fancy, but hide it well and make it scary and you shouldn't have many problems.
- sarciszewski 11y agoRight, well, "constant strings" don't really exist in scripting languages, and before you mention objects that encapsulate them, what's to stop developers from building them elsewhere before passing the string to an object that encapsulates them? Chicken and egg. Education is the security strategy that pays forward the most.
- mikeash 11y agoApologies, I meant string literals. There's no technical reason the language couldn't make it so that string literals can be identified at runtime. There are several good ways to put a stop to SQL injection. Better education is one, better APIs is another. There is no reason to just give up on the idea of using SQL queries directly because of injection attacks.
- sarciszewski 11y agoPop quiz: What is the singular cause of SQL injection, XSS, and stack overflows that causes a security vulnerability? ... The answer is: Data being treated as an instruction. Solution: Separate them so that data can never be interpreted as an instruction! In SQLi, this solution is to use parameterized queries. You send the query in one packet, then the parameters in a second one. SQLi is thus prevented. (Not that SQLi is the only vulnerability possible.)
- mikeash 11y agoI'm not sure what your point is here, because what I'm advocating is precisely to make it easier to use parameterized queries and more difficult not to.
- sarciszewski 11y agoI wasn't being argumentative, I just thought that would be something worthwhile to add for people following along.
- EGreg 11y agoDo you know any good PHP database frameworks that help you build up the query programmatically? Over the years, we built one in-house at http://qbix.com/platform/guide/database http://qbix.com/platform/guide/database because we couldn't find one. It does things like sharding out of the box, because it's able to understand the query's criteria and target it to the right shards.
- trm42 11y agoLaravel at least has Query Builder: http://laravel.com/docs/5.1/queries http://laravel.com/docs/5.1/queries As an interesting side note: Laravel's AR ORM (=Eloquent) is built on top of the Query Builder so you can leverage Query Builder with AR if you know what you're doing. Can also lead to big problems if you don't realize the possibilities and the caveats.
- EGreg 11y agoLike what kind of problems?
- trm42 11y agoHere has been discussion in another comments about overusing ARs for trying to achieve too complicated things that'll lead to DB performance problems. As stated elsewhere here, AR is good for CRUD operations but after that you should use Query Builder or plain SQL to achieve the more complicated cases.
- wmil 11y agoAt the same time I understand why it would be more confusing for newbies. In a language that already has string interpolation you're telling them to use a crappier custom version of string interpolation that's safe for databases. Tutorials need to be more upfront about that.
- sarciszewski 11y agoI've got "writing an open access PHP 7 online book to hopefully serve as a new, best-practices tutorial" next on my to do list.
- sopooneo 11y agoBut if the example above uses PDO underneath, then it's not just string interpolation though I think. It's sending the query and the parameters separately to the database, which is creating a "prepared data object" to plug the parameter values into. I may be wrong, so please correct me if so.