4 ms·
Universities and managers must do a little bit of torture, spanking and ruler on the knuckles every time they see a student or a new dev not parameterize a quer
by cm2187 9y ago
Universities and managers must do a little bit of torture, spanking and ruler on the knuckles every time they see a student or a new dev not parameterize a query. It must become a reflex like watching for cars before crossing a street.
- amatera 9y agoSome weeks ago an experience developer said to me: "Parameterize a query? Since years i don't care about it, because ORMs like Doctrine or Sequelize take care of that"... So it's not only students or new devs who should watch out, because even ORMs can open up SQL injections.
- Edmond 9y agoWhat is needed are modern dev tools that can automate away some of these boilerplate issues...something like this: http://www.codesolvent.com/static-assets/gif-studio/datastatement/shot.webm http://www.codesolvent.com/static-assets/gif-studio/datastat... It forces parameterization on all queries, you'll have to go out of your way to create a statement that doesn't parameterize it's inputs. Disclosure: I am the developer of Solvent.
- geekamongus 9y agoTool are good to assist, but a fundamental understanding of SQLi and how to prevent it are imperative for developers to understand.
- Edmond 9y agoAgreed, knowing the fundamentals is always preferable...but that is unfortunately a high-bar for most "developers" so it is better to have solutions with builtin guardrails.
- krylon 9y agoOnce I learned one can parameterize queries, not doing so began to feel kind of dirty to me. To me, the big point was not even security, but the fact I no longer had to deal with properly escaping/cleaning messy input. Plus, somebody once told me parameterized queries can (potentially) give better performance. What's not to love?
- zbentley 9y ago> parameterized queries can (potentially) give better performance. Yep! More accurately, parameterized queries almost always give better performance. The simplest way parameterization helps (and there are many) is query plan caching. When a DB server sees a query for the first time in awhile, it has to turn the SQL text into a query plan (https://en.wikipedia.org/wiki/Query_plan https://en.wikipedia.org/wiki/Query_plan). That takes time and resources (lexing, parsing, validation against schema, etc.). In most RDBMSs I know of, query plans are cached by default for awhile after they are made and executed. The plans also contain placeholders for parameters rather than inlining the parameter values supplied with the query. This means that running the same query with different parameter values usually allows the database re-use the cached query plan and just substitute different parameters, which saves time on plan construction. Plan caching + parameterization saves time on a ton of other stuff, too: re-running the same plan for multiple queries allows automatic tuning and heuristic analysis to get better performance of the query over time, facilitates predictive caching of result data (from index warming to full row caching), and a lot of other cool stuff. Aside: you also occasionally see some silly tricks used to defeat plan caching. For example, say you have a complex query, hitting multiple indexes, that gets run all the time with different parameters. Most runs return very few results, and the RDBMS optimizes the cached query plan for that case. Now, say you as a developer know that one particular parameter value for is going to return a lot of results, leading to extremely inefficient index scans, and that the optimized-over-time, cached version of the query is probably going to do the wrong scans, taking an unnecessarily long time. Instead, the naïve "rebuild plan from scratch" approach would yield better performance. Now, if you're right about that (and second-guessing the query planner is a really bad idea on problems like this; the people who write it are almost always smarter than you), you have a few options: The "correct" choice usually cited is to add RDBMS-specific "hints" to your extra-big query text instructing the database on which indexes to use, or to ignore cached plans. That might be a skill you don't have, or you might not know which ones it should use, just that the naïve approach is better. There is usually a way to "flush" cached query plans from the database, but that is usually a very bad idea to do programmatically, may require administrator access, and very possibly compromises performance of the "fast" versions of the query running in parallel. Instead of those, what I've seen a lot of people do is just defeat the plan cacher. Since the query plan cache is often keyed by parameterized text (e.g. somewhere in the RDBMS there's a hash table mapping "SELECT * FROM TABLE WHERE FIELD = '?'" to query plan objects), I have seen folks add pointless "1 = 1"-type clauses to their text, or just add "unique-ing" comments to the beginning. This has the advantage of leaving other cached plans alone and forcing a re-plan from scratch. It's also undeniably a bit of a hack, but a very useful one to be sure.