Quoting table and column names in SQL query

The problem is raised In this issue https://github.com/y.../yii/issues/513. I am thinking to add support this. In particular, if we pass in a SQL statement like this to DB Command: SELECT [[id]] FROM {{city}}, then it will be automatically translated for MySQL into: SELECT id FROM city. That is, anything enclosed within {{ and }} is treated as table name and will be properly quoted (and prefixed if tablePrefix is set); and anything enclosed within [[ and ]] is treated as column name and will be properly quoted.

Note that this feature does have the side effect that it may break SQLs that use these tokens as normal string values (e.g. … WHERE keyword LIKE ‘%[[something]]%’). Of course, such side effect can be worked around by using parameter binding.

What do you think about this feature? It will make writing cross-DBMS applications easier.


vote+=1;

That’s a good thing to add for product-type projects. For regular ones it’s not that useful.

There are two cons:

  1. IT can possible slow DB layer down.

  2. You’ll not be able to copy-paste SQL from code and run it your DB console or manager directly. Not a huge issue.

Questions:

  1. Why [[something]] and not [something]?

Should be configurable like the table prefix feature.

If turned off, no replacing should be done.

As samdark said, very nice for applications that want to support multiple dbs but not so nice for applications using one dbms.

I don’t see a problem with this… why would there be an option for this… if you don’t want the quoting you just don’t use the [[…]] and/or {{…}}… or if you use one dbms you can just use the quote you know that are available for that dbms…

I guess the performace speed is not in question too as the quoting would be processed only if there are "placeholders" for that, if not it would be processed standard way.

I agree that there’s no point in turning it on and off. If it can be turned off projectwide, then it will not be safe to use it in extensions where it’s really important.

The reason for using double brackets is to reduce the chance of converting normal expressions (even though I don’t know if any DBMS supports the usage of something like {abc} or [abc]). Actually SQL server does use [abc] for quoting table/column names.

I would like to hear more opinions about this before taking action.

Would it make sense to use mysql style backquote and then convert it to proper dbms quote ?

Okay, agree with that. So I am fine with everything :slight_smile:

In the Italian keyboard layout, the backquote doesn’t exist (indeed I always use the single quote ’ ).

+1 for the [[ ]] solution.

qiang

[] is used in MSSQL for exactly the same purpose. That’s their own SQL extension and as far as I know, square brackets aren’t used in other DBMs so it’s safe to use just []. Less typing.

[ ] is used in MSSQL as a sort of escape function, so you can use reserved words inside [ ] etc… Maybe I want to use [ ] for MSSQL statements without triggering Yii capabilities… and it would be confusing. Moreover, I prefer [[ ]] for consistency with {{ }}.

Yes, you’re right. I haven’t thought about just keywords.