0

For example, in pure PHP this below link demonstrates problems with naming columns with reserved words

Syntax error due to using a reserved word as a table or column name in MySQL

However, I am using both 'key' and 'value' as column names in several tables in my application which I have developed for 15 months now and never did I have any single problem with these names. I probably use some more reserved words for the column names.

Is Laravel escaping them with backtick as the answer suggests one should do manually? If Laravel is not escaping them how come I can use any name for the column in a table and not get any problems when I create or update my model?

niko craft
  • 2,893
  • 5
  • 38
  • 67
  • Conservatively, there are approximately 180,000 words in current usage in English, of which, MySQL has seen fit to reserve around about 200. There really is no need to use a reserved word as a table/column identifier. To do so is just asking for trouble. – Strawberry Oct 29 '18 at 23:40
  • Note that value is not a reserved word. – Strawberry Oct 29 '18 at 23:40
  • Just don't, it is always far more trouble than it is worth, and as well noted above there are just so many ways to avoid it. tip: think "of what" when choosing a name e.g. key of what? value of what? date of what? – Paul Maxwell Oct 29 '18 at 23:52
  • I got a settings table with key and value as column names, these are perfect table names for settings table. If I put something like 'identifier' it just stupid, longer to type and does not make sense for this particular table, and what should I use instead of 'value' as column name in this case were its the only name that makes sense? It's not just the table, I use this in VueJs and other parts of code and having to use stupid names that do not make sense when you read the code just because mariadb and mysql reserved the worlds but Laravel properly escapes it makes no sense. – niko craft Oct 29 '18 at 23:59

2 Answers2

3

You should never name your columns with key words, it's asking for trouble.

Sure it might work; for a while, maybe for ever, but maybe not anymore on a day where you really don't need such an issue, or years from now, when you forgot all about it.

Compare it with driving a car with your eyes closed. That works for a while quite fine, too.

Aganju
  • 6,295
  • 1
  • 12
  • 23
  • 1
    Extreme example.. But i agree.. :D – dwir182 Oct 29 '18 at 23:38
  • It's also so your co-workers like you instead of loathing you. Conflicting keyword column names are extremely annoying to have to work around, and they can almost always be avoided. Using them is a sign of not caring. – tadman Oct 29 '18 at 23:44
  • I just found out that Laravel properly escapes it. I'm in also working with a client where they use weird words like identifier instead of key. Since Laravel makes it work, I preffer to use key instead type identifier. If this was pure php project then your feers would have been valid. For last 15 months on my own project I did not have a single problem with reserved keywords for table names – niko craft Oct 29 '18 at 23:55
1

Short answer: Yes it can be problematic, but yes Laravel is quoting them properly in most cases. (Cases where it doesn't would most likely be in raw queries)

If you can help it, use words that aren't reserved. For example, order can be named sort_order.

I've worked on a non-laravel project where a column really was named order. Quoting properly with backticks (`order`) solved the MySQL syntax problems that were caused by running queries such as select * from foo order by order. However, I encountered multiple different workarounds for this simple problem implemented by developers who weren't familiar with either PHP, MySQL, or both of their syntax. Sure the proper fix was simple (backtick quotes), but someone without the same background might (and did) come up with a convoluted solution to a problem that didn't need to exist in the first place.

Also, column names with spaces... *shudders*

Travis Britz
  • 5,094
  • 2
  • 20
  • 35
  • I got a table named settings where it makes much sense to have two columns named 'key' and 'value', its also so that these names are used on vuejs side and inside php classes, I've not had a single problem, and now everyone is saing oh no do not do that. To me it makes perfect sense to use "key" and "value" as column names, its only logical thing in this case where table is named settings, especially since Laravel takes care of it and makes it work. I could use settings_key, settings_value, but that just makes no sense and it makes for much more typing – niko craft Oct 30 '18 at 00:03
  • 1
    There's nothing stopping you from doing it. If the column names are *always* quoted properly then you (and whoever takes over for you) might never encounter these kinds of errors. If you understand what kind of risk it introduces and you're still ok with it, then go ahead with your choice of doing it anyway. – Travis Britz Oct 30 '18 at 00:12
  • Is it wrong to have much more typing? Is it wrong to have identify column different with other? – dwir182 Oct 30 '18 at 00:12
  • Travis that is the right answer, the follow up to my comment, thanks :) – niko craft Oct 31 '18 at 11:25
  • Dwir182 It's wrong when you have to do this $setting->setting_key, $setting->setting_value. When working on a project that takes 2 years to do, that's a lot of extra typing for something that is used everywhere and it looks unprofessional, $setting->key, $setting->value looks better, more professional, etc etc – niko craft Oct 31 '18 at 11:26