When to use or not to use quoteName() ?

[rolandd]

Hello all,

The other day I had a discussion with someone about the usage of the quoteName() method. I basically quoteName() every field and tablename I come across, while the other person suggested it is only really needed for table names. By using it only on table names we save some overhead. My thinking was that with all the different supported databases, the quoteName() on everything is more of a must than an option.

Is one way better than the other or is it more a personal thing?

Would like to hear your input on this.

[Evandromar Machado]

Hello,

I only use QuoteName() for tables, and Quote() in strings where....

$query->select('c.title'); //  Not necessary quote c.title

$query->from($db->qn('#__content', 'c'));

$query->where('c.title LIKE ' . $db->q('%test%'));

[Mark Dexter]

I believe it is best practice to do as you originally suggested and use it on table and column names. If you know for certain that a table or column name will never require quoting -- in any supported database -- you can skip it. However, you do this at your peril. For example, MySQL and other db's have been known to add new reserved words with new versions. If you were unlucky enough to use one of these words as a table or column name, you would be introducing a time bomb into your code.

As far as I know, the overhead associated with quote() or quoteName() methods is trivial and I very much doubt that you could measure it.

Imo, it would be really great to somehow build in these methods into the various query methods so you could optionally have the select() and other methods do the quoting for you. 

Mark

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-gene...@googlegroups.com.
To post to this group, send an email to joomla-de...@googlegroups.com.
Visit this group at http://groups.google.com/group/joomla-dev-general.
For more options, visit https://groups.google.com/groups/opt_out.

[Herman Peeren]

quoteName() is used to be able to use reserved words (like: table, count etc.) in identifiiers (such as: table names or field names) or special characters. It is resolved in different manners in different databases: with a back-quote (back-tick) in MySql, with square brackets in SQL-server, with a double quote in standard SQL (whatever that may mean).

If you use quoteName() for fields, you can use reserved words or special characters.

[rolandd]

Thanks for the feedback all :)

Good to know that the overhead is minimal. However I do agree that it would be good to have automated name quoting in the JDatabaseQueryBuilder. I mean, if we are quoting already we might as well make it default, does save a lot of code :) The only problem I see here is that at this moment, it is not quoting by default, were we to introduce this, a lot of code might be double quoted and that sounds real bad. Any ideas how we should go about this?

[Mark Dexter]

I'm not sure the best way to do this. One way would be to introduce new methods that include quoting. For example, something like: selectQuoted() or something. This would not be able to allow the current usage of 'a.*', but could allow comma-delimited lists of column names. (You would need to split the column names, add the quotes, and put them back together inside the quoteName() methods.)

Another option would be to add a new argument that defaults to false. So something like select($columns, $autoQuote = false). Again, if you used something like 'a.*' for the columns, quoting could not be used, so I'm not sure if this would be good or not. 

Maybe there are other options as well. Off the top of my head, adding new methods would seem to be the simplest. They could simply call the existing methods after the quoting has been added. So you would be able to do this:

$query->selectQuoted('a.id', 'a.name', 'a.address');

instead of this:

$query->select($db->quoteName('a.id'), $db->quoteName('a.name'), $db->quoteName('a.address'));

Again, I don't think you would be able to do $query->selectQuoted('a.*'), the way you can now with select(). 

Mark

On Sun, Sep 1, 2013 at 1:28 AM, rolandd <rdalm...@gmail.com> wrote:

Thanks for the feedback all :)

Good to know that the overhead is minimal. However I do agree that it would be good to have automated name quoting in the JDatabaseQueryBuilder. I mean, if we are quoting already we might as well make it default, does save a lot of code :) The only problem I see here is that at this moment, it is not quoting by default, were we to introduce this, a lot of code might be double quoted and that sounds real bad. Any ideas how we should go about this?

--