Whats the point of query builders in scripting languages

A place to discuss the implementation and style of computer programs.

Moderators: phlip, Moderators General, Prelates

User avatar
psykx
Posts: 408
Joined: Sat Feb 23, 2008 11:24 pm UTC
Location: England
Contact:

Whats the point of query builders in scripting languages

Postby psykx » Fri Sep 26, 2014 8:48 am UTC

Hi there,
I'm working on a new system and one of the coding guidelines is to use the Yii query builder (the project is PHP and MySQL). I'm not a fan of this as I don't see any advantages to using a query builder when you already have to know SQL to a high level to work on the project. For me it's simpler just to read SQL code. Can you tell me the good points of using a query builder?
Berengal wrote:Only if they're killer robots. Legos are happy robots. Besides, even if they were killer robots it wouldn't stop me. You can't stop science and all that.

KnightExemplar
Posts: 5494
Joined: Sun Dec 26, 2010 1:58 pm UTC

Re: Whats the point of query builders in scripting languages

Postby KnightExemplar » Fri Sep 26, 2014 1:46 pm UTC

The point is not for you to build queries. The point is so that your users can build queries.

You parse the user's commands from a form, grab out the "AND" and "OR" statements, build a SQL query and let it rip in your database. The "build an SQL query" part however is a bit difficult with raw text strings... and also dangerous from a security point of view.

Learning to use an SQL Query Builder is absolutely necessary if you wish for users to build custom queries against your database.

Imagine building this feature from Google into your database.
First Strike +1/+1 and Indestructible.

User avatar
Xanthir
My HERO!!!
Posts: 5336
Joined: Tue Feb 20, 2007 12:49 am UTC
Location: The Googleplex
Contact:

Re: Whats the point of query builders in scripting languages

Postby Xanthir » Fri Sep 26, 2014 7:34 pm UTC

No, I don't think that makes much sense, KnightExamplar.

Query builders exist because they translate SQL into a close-enough form that your language actually recognizes, because your language doesn't handle SQL natively. They let you interact with a SQL query at a level above string manipulation, which is notoriously hard to get right and secure. This isn't just about "users" getting things wrong (or being malicious), it's about the actual application developers doing things wrong, not escaping things correctly, etc. Having instead an interface based on template parameters and function arguments means you dont' have to worry about all that stuff - the builder will do the correct escaping for the context for you.

In the case of Yii and many other builders, it also lets you build up a query piecewise without having to worry about getting all the SQL syntax in exactly the right order. You can set the FROM before the SELECT, if that's simpler for your app's structure.
(defun fibs (n &optional (a 1) (b 1)) (take n (unfold '+ a b)))

Ubik
Posts: 1016
Joined: Thu Oct 18, 2007 3:43 pm UTC

Re: Whats the point of query builders in scripting languages

Postby Ubik » Fri Sep 26, 2014 8:23 pm UTC

Another thing they allow is to hide the differences between the database engines, which is something database wrappers like to do. Just having a relatively simple thing like pagination without some common intermediate way to represent queries would mean having to write separate queries depending on what database you're running on, or being locked on the current database of choice.

User avatar
psykx
Posts: 408
Joined: Sat Feb 23, 2008 11:24 pm UTC
Location: England
Contact:

Re: Whats the point of query builders in scripting languages

Postby psykx » Sat Sep 27, 2014 10:34 am UTC

Xanthir wrote:In the case of Yii and many other builders, it also lets you build up a query piecewise without having to worry about getting all the SQL syntax in exactly the right order. You can set the FROM before the SELECT, if that's simpler for your app's structure.


Simply manipulating strings would allow you to easily do this too. Imagine code that looked like $db->query( $select . $from . $order ); you would just need to populate the strings in a different order.

I understand the danger in not escaping strings, although this can easily be counteracted in other ways for example using PDO in PHP.

My original point is that surely for anyone with experience working with SQL, using strings rather than a query builder object is simpler and more readable. It's also more uniform between frameworks. I do agree that using a query builder allows you to use different database engines, but I've never seen or heard of this happening or needing to happen in 6 years of web application development over 5 companies.
Berengal wrote:Only if they're killer robots. Legos are happy robots. Besides, even if they were killer robots it wouldn't stop me. You can't stop science and all that.

User avatar
PM 2Ring
Posts: 3653
Joined: Mon Jan 26, 2009 3:19 pm UTC
Location: Mid north coast, NSW, Australia

Re: Whats the point of query builders in scripting languages

Postby PM 2Ring » Sat Sep 27, 2014 1:44 pm UTC


User avatar
Mat
Posts: 414
Joined: Fri Apr 21, 2006 8:19 pm UTC

Re: Whats the point of query builders in scripting languages

Postby Mat » Sat Sep 27, 2014 4:32 pm UTC

Something like $select . $from . $order doesn't work well when your queries get more complex, especially if you are supporting more than one database.

For example, one query might need to be compiled to any of these:
  • select top 10 ...
  • select first 10 ...
  • select ... limit 10
So you would need a conditional statement around your final concatenation, conditional statements where you build the parts, and you need to properly escape every dynamic variable that gets substituted in. This is not particularly readable and it is very easy to introduce bugs this way. It's also pretty tedious to have stuff like this scattered all over your code. I used to maintain something like this and it basically evolved towards a less flexible, non-OO version of a query builder anyway.

On the other hand, the first example in the documentation you linked to is this:

Code: Select all

$user = Yii::app()->db->createCommand()
    ->select('id, username, profile')
    ->from('tbl_user u')
    ->join('tbl_profile p', 'u.id=p.user_id')
    ->where('id=:id', array(':id'=>$id))
    ->limit(10)
    ->queryRow();


Even though I've never used this library before, as a SQL user I can reason about what it's doing, and all the meaningful code is in one statement, which lets me see that there's no SQL injection issues in this query without having to trace data through multiple lines of code.

Another place SQL strings fall short is nested expressions. A really common mistake I have when writing long SQL queries is missing out parentheses when dealing with ANDs and ORs, or just failing to balance the parentheses properly. SQL is terrible for this, as to represent something that is essentially a tree you have to write it as one big chunk of plain text, which is not inherently tree like. When this is just a string variable in your code you don't have proper syntax highlighting to fall back on, and the hierarchical structure of your query is invisible to string operators.

With a decent query builder you can just extract the subexpressions to separate variables, and you can combine these however you like without introducing syntax errors.

DR6
Posts: 171
Joined: Thu Nov 01, 2012 1:44 pm UTC

Re: Whats the point of query builders in scripting languages

Postby DR6 » Sun Sep 28, 2014 12:30 pm UTC

If you make queries dynamically by directly generating SQL query strings, you will be implementing what Yii already does, except Yii already handles cleanly all the edge cases and gotchas that will bite you if you do it by hand. If you are only generating very simple queries it may work, but for moderate to complex things you will regret it. At that point, you will either mix the SQL generating logic and the logic of your program(which will less readable than using a builder) or you will abstract the SQL generating logic, so the end result will be exactly like using Yii but worse.

korona
Posts: 495
Joined: Sun Jul 04, 2010 8:40 pm UTC

Re: Whats the point of query builders in scripting languages

Postby korona » Sun Sep 28, 2014 12:52 pm UTC

To be honest I don't think that abstracting over different database engines makes much sense. In my experience you usually have to use database specific features for performance optimization anyways.

KnightExemplar
Posts: 5494
Joined: Sun Dec 26, 2010 1:58 pm UTC

Re: Whats the point of query builders in scripting languages

Postby KnightExemplar » Sun Sep 28, 2014 1:00 pm UTC

korona wrote:To be honest I don't think that abstracting over different database engines makes much sense. In my experience you usually have to use database specific features for performance optimization anyways.


This. For example, if you're using PostgreSQL in a modern setting, you probably want to use the MongoDB-performance crushing JSON storage engine.

http://vibhorkumar.wordpress.com/2014/0 ... -9-3-json/

Why "cache" with a MySQL + MongoDB solution when staying inside of PostgreSQL is significantly faster and easier?

Also, CTEs, recursive queries, etc. etc. Even something as simple as Date/Time handling and authentication quickly becomes database specific. (PostgreSQL can support "authentication" by user process, bypassing any password being stored in your web app at all. Date/Time can be stored with Timezone information in PostgreSQL but not MySQL). Raw SQL is more expressive and efficient when you start using platform specific optimizations.

Now, I'm not calling query builders dumb or anything, but the only time I can think of ever using them is when I'm somehow interfacing with user input. There is indeed some security benefit to a query builder, but honestly Prepared Statements are bulletproof as well. Of course, a query builder is easier to use than prepared statements... and security of prepared statements varies between implementations...
First Strike +1/+1 and Indestructible.

User avatar
Xanthir
My HERO!!!
Posts: 5336
Joined: Tue Feb 20, 2007 12:49 am UTC
Location: The Googleplex
Contact:

Re: Whats the point of query builders in scripting languages

Postby Xanthir » Mon Sep 29, 2014 7:22 pm UTC

KnightExemplar wrote:Now, I'm not calling query builders dumb or anything, but the only time I can think of ever using them is when I'm somehow interfacing with user input. There is indeed some security benefit to a query builder, but honestly Prepared Statements are bulletproof as well. Of course, a query builder is easier to use than prepared statements... and security of prepared statements varies between implementations...


This. Ignoring all the other theoretical benefits, I found query builders so useful (despite being a highly skilled programmer and highly comfortable with SQL) that I *wrote my own simple one* rather than write raw SQL. There's just too much opportunity to screw up because you forgot to quote or escape something properly. And even if you do it all correctly, the escaping is significant visual noise, which is removed by a query builder.

(In the absence of a good type system letting you differentiate between "clean" and "unclean" strings, you want to pass data around unescaped, and only escape it at the last moment, while you're generating the query. Otherwise, you risk confusing which strings are clean and which aren't, and letting unescaped strings into your query, or double escaping them, or escaping a string early then using it in some other context where the escaping is ugly clutter or an actual error...)
(defun fibs (n &optional (a 1) (b 1)) (take n (unfold '+ a b)))


Return to “Coding”

Who is online

Users browsing this forum: No registered users and 8 guests