Yii Framework Forum: Quoting table and column names in SQL query - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Quoting table and column names in SQL query

#1 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,900
  • Joined: 04-October 08
  • Location:DC, USA

Posted 28 May 2012 - 07:54 PM

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.
1

#2 User is offline   rootbear 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 222
  • Joined: 17-June 11

Posted 28 May 2012 - 08:42 PM

vote+=1;

I enjoy the Yii.sy coding life here.
0

#3 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,778
  • Joined: 17-January 09
  • Location:Russia

Posted 29 May 2012 - 03:43 AM

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]?
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#4 User is offline   CeBe 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 534
  • Joined: 16-July 10
  • Location:Berlin. Germany

Posted 29 May 2012 - 06:36 AM

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.
0

#5 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,359
  • Joined: 12-October 09
  • Location:Croatia

Posted 29 May 2012 - 05:27 PM

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.
Find more about me.... btw. Do you know your WAN IP?
0

#6 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,778
  • Joined: 17-January 09
  • Location:Russia

Posted 29 May 2012 - 07:47 PM

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.
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#7 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,900
  • Joined: 04-October 08
  • Location:DC, USA

Posted 29 May 2012 - 07:53 PM

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.
0

#8 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,359
  • Joined: 12-October 09
  • Location:Croatia

Posted 30 May 2012 - 02:07 AM

Would it make sense to use mysql style backquote and then convert it to proper dbms quote ?
Find more about me.... btw. Do you know your WAN IP?
0

#9 User is offline   CeBe 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 534
  • Joined: 16-July 10
  • Location:Berlin. Germany

Posted 30 May 2012 - 03:14 AM

View Postmdomba, on 29 May 2012 - 05:27 PM, said:

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.

Okay, agree with that. So I am fine with everything :-)
0

#10 User is offline   ekerazha 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 526
  • Joined: 10-October 08
  • Location:European Union

Posted 30 May 2012 - 03:16 AM

View Postmdomba, on 30 May 2012 - 02:07 AM, said:

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


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

+1 for the [[ ]] solution.
Yii user #37
0

#11 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,778
  • Joined: 17-January 09
  • Location:Russia

Posted 30 May 2012 - 03:29 AM

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.
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#12 User is offline   ekerazha 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 526
  • Joined: 10-October 08
  • Location:European Union

Posted 30 May 2012 - 03:41 AM

View Postsamdark, on 30 May 2012 - 03:29 AM, said:

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 {{ }}.
Yii user #37
0

#13 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,778
  • Joined: 17-January 09
  • Location:Russia

Posted 30 May 2012 - 04:12 AM

Yes, you're right. I haven't thought about just keywords.
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users