Why is codeigniter using backtics in mysql code?

[eluser]Bart v B[/eluser]
Hi all..

I am a little concerned.
Everbody knows that My alows you to put ->`<- back-ticks around column names.
The main reason is that alows you to use reserved words in your sql statement.

Thats a little bit strange because if it is a reserved word, why you should use it?

So i was thinking, (i did not digg into the codeigniter code yet)
why we do it in a proper way and get rid of that piece code that alows that,
and instead we give a big error if its a reserved word.

I think thats a better approach.

How do you think aboute it?

[eluser]heavenquake[/eluser]
Whether to use reserved words or not should be up to the individual developer/team, not the framework. The framework, CodeIgniter in this instance, is not meant to dictate your workflow, only enhance it. CodeIgniter is a toolbox, not a set of rules.

[eluser]danmontgomery[/eluser]
A more appropriate way of putting this would be that MySQL allows you to omit the backticks in certain situations, not the other way around.

[eluser]Jelmer[/eluser]

I am a little concerned.
Everbody knows that My alows you to put ->`<- back-ticks around column names.
The main reason is that alows you to use reserved words in your sql statement.
Thats a little bit strange because if it is a reserved word, why you should use it?

What about that concerns you? For example: when I use a column named "order" to order pages on something other than page titles or date I'd like it to be named "order" and not "page_order" in my table called "pages" because that's just clutter that doesn't add any information. Why would I be afraid to call it "order"? It poses no risk whatsoever.

Personally I add backticks even when I write queries myself, mainly because to me it makes the query better readable. I would expect that using backticks is the prefered way and not using them is allowed short-hand. But that's for someone who knows more about databases then I do to answer.

Bottom line: what's the problem with using backticks? Other then you being afraid that it allows you to use mySQL reserved words as column names, something most user would consider a feature.

[eluser]Bart v B[/eluser]
My thought was, ok an application needs to be wide and save.
So when i want to use an other database, and the backtick isn't used,
You get an big problem.

For example:

i have in my database used:

SELECT
`delete`
FROM
tabel;

That is what mysql, (and codeigniter by default) allows you to do.
That's dangerous because when i want to use another framework or someone go a procedural way, then you would have a problem.

SELECT
delete
FROM
tabel;

A whole table is empty then.
Normal you never do that if your have more experienced.
But when a starter whats to use this framework, with just a little knowledge of databases
has a big problem.

And that is my concurring.

[eluser]n0xie[/eluser]
So your concern is that programmers can do stupid things if they don't know what they are doing?

[eluser]Bart v B[/eluser]
[quote author="n0xie" date="1267195943"]So your concern is that programmers can do stupid things if they don't know what they are doing?[/quote]

Something like that. ;-)

There is a manual for codeigniter, there is also explaned that how you can make youre code beter. For example, XSS. So why not explain that its a beter approce to do not use reserved words. It does not make sence. The only thing that i can find in the ci manual is that ci when using a Mysql database create magical backticks. It's strange in my opinion.

:coolsmile:

[eluser]n0xie[/eluser]
The backticks are not magical and are there for a reason. If for some reason you use some weird naming convention in your DDL, you need an escape character (backticks). For instance the following statement creates a table named a`b that contains a column named c"d:

Code:

CREATE TABLE `a``b` (`c"d` INT);

This is perfectly legal in MySQL and has nothing to do with reserved words. The option of being able to use reserved words is just a symptom of this.

Now you might say, who would ever use ` or ' in column/table names? To which my response would be, who would ever use reserved words as column/table names?

[eluser]heavenquake[/eluser]
[quote author="Bart v B" date="1267200400"][quote author="n0xie" date="1267195943"]So your concern is that programmers can do stupid things if they don't know what they are doing?[/quote]

Something like that. ;-)

There is a manual for codeigniter, there is also explaned that how you can make youre code beter. For example, XSS. So why not explain that its a beter approce to do not use reserved words. It does not make sence. The only thing that i can find in the ci manual is that ci when using a Mysql database create magical backticks. It's strange in my opinion.

:coolsmile:[/quote]

The only thing CI xss-cleans by default is the uri segments. Post-data etc. still needs to be xss-cleaned manually through a second boolean parameter. Unless of course you activate global xss-filtering. I agree that it would make sense to have the possiblity to activate global "no backticks" too, though. But you have got to understand that CI is not a set of rules to follow, it is a toolbox that allows you to set your own rules, and adapt it to your workflow. If you need code to tell you what to do and what not to do, why are you even coding? :0)

[eluser]Jelmer[/eluser]

So when i want to use an other database, and the backtick isn’t used

That's a valid concern in many cases, but I must admit I've never tried to remain compatible with more then mySQL. So if you need that compatibility you might want to refrain from using reserved words, other than that I still don't see a problem with CI using backticks.

That’s dangerous because when i want to use another framework or someone go a procedural way, then you would have a problem.

SELECT
delete
FROM
tabel;

Ok, there's an intereseting example - though I would argue that this is one of a very few cases where using reserved words is actually very dangerous. And also that's still the responsibility of the programmer, CI allowing it is not a problem.

Normal you never do that if your have more experienced.
But when a starter whats to use this framework, with just a little knowledge of databases
has a big problem.

The starter that's going to switch databases is going to have more to worry about then just backticks in all likelyhood. Also if you're going to risk code like your example you're bound to make much larger and more dangerous mistakes.