DB, table, column names without auto '`' back-tick

Hi,

I am looking for how I can get Heidi SQL to NOT insert this ugly '`' GRAVE ACCENT = back-tick around db names, table names and columns.
NB: in older Version Heidi did not do that.
Now (3892) when you type e.g. the "." after a db Name, then select a table from the list that table name is quoted by '`' also when you double click in the left pane.
e.g.:
select * from `ncrecs`.`al_transactions`
what I want is
select * from ncrecs.al_transactions

1st it's ugly :-)
2nd the auto coloring for table names (Fuchsia) no longer apply it looks like columns (Olive)
3rd I never use spaces in my identifiers - old school I know :-)
4th '`' is hard to type on my Keyboard layout

Oh there is one other thing is there a way to influence the SQL auto reformat function? I especially dislike that e.g. "LEFT OUTER" is on a separate line from the "JOIN" and indenting depending on the bracketing level ....

Thanks
Robert
Otherwise I love Heidi

Identifiers are only quoted if they contain critical characters. I just changed this behaviour in r3893 so that also underline characters do not cause backticks to come in. In other cases backticks are required, e.g. for identifiers with the dash in them.

Auto-reformatting has no options.

SUPER - exactly as I wanted it Thanks!!!!

Servus
Robert

Oh, that could be so great if it was possible to configure both things - '`' and reformat. The first I would say is important (as much as autocomplete), the second desirable. Habit to use '`' everywhere helps to fight some weird SQL injections it is too easy not to think off while scripting. Desperation for aesthetics, laziness to press additional key, weird locations on keyboard are all "good" reasons why SQL injections work so good in hacking community and why tools capable to assist for developer to oppose them are so needed.

I would say that nerf of feature is not a solution, solution could be fixing a code highlighter and automate use of "`" enough that there were no real need to type it.

Sorry for tone, I saw a little bit too much owned sites just because for someone "`"s were ugly.

Habit to use '`' everywhere helps to fight some weird SQL injections

I doubt you can provide a valid example that illustrates that idea.

The proper way to prevent SQL injection is to stop composing dynamic SQL from untrusted input using string manipulation functions and use instead your client library's prepared statements feature.

You are absolutely correct saying, that fighting injection is everything about validation. But there is scenario, where back-ticks could save a day. Maybe i tried to say too much with too few sentences. My bad.

Problem is that lots of programmers do not know what and how to escape to be safe. It is not rare that they even do not know a difference between validation and escaping. I would say there are two big groups of weak spots in SQL statements:

1. Values. As an example "SELECT * FROM `test_tbl` WHERE `id` = ?".

Prepared statements can help here as that is one of things what they are intended to be used for - multiple runs of the same query with different values as parameters and save developer's time and fingers for proper escaping the value. But.. You know, everything should be tested before use. In example there was time when everybody thought that they are safe with PHP's mysql_escape_string. Back-tick causes here trouble if value was not single-quoted, so target of interest are numeric values which potentially can be unquoted.

2. Query structure. When other parts of query are dynamically built, not just value specified. Typical use cases: advanced search forms, various grids and similar. I do not think that prepared statements can help here, but I may be wrong. Simply I do not use them for escaping. I prefer to clean incoming data at request handling. For my taste SQL is too deep in the model for this. Examples (image it is PHP):

"SELECT `{$colLeft}`, `{$colRight}` FROM `testtable`"
"SELECT * FROM `{$tableName}`"
"SELECT * FROM `testtable` ORDER BY `{$columnName}` DESC"
"SELECT * FROM `testtable` ORDER BY `id` {$order}"

Here everything what is important is to ensure, that variables used in statements can have a limited set of valid values. Code should analyze user input and choose predefined value for variable. In such way user has no direct access to variable and it's value. So, there is no need to make proper escaping, just have validation. If you do this, both:

a) "SELECT `{$col}` FROM `testtable`" and
b) "SELECT {$col} FROM `testtable`"

are equally safe from injection. Problem is that those who are lazy enough to use back-ticks tend to be the same lazy to spend time for implementing isolation and use the same techniques as for values (if use anything). So:

* if user input reaches query definition not altered, both cases above are injection vulnerable.
* if programmer had made escaping, for example used PHP's mysql_real_escape_string, both cases are injection vulnerable.
* if programmer in addition to escaping, had filtered out back-tick, in B scenario he is vulnerable, in A safe. Of course, if it can be called "safe". When seeking for holes ability to crash is always desirable, just not always useful. Crash messages can give useful information, but it should not be a problem in properly configured production environment. In A case user is not safe from crashing.

That was a case which I had in mind when talked about owned sites as problem existed in one of CMSs. Back-ticks have more, maybe even more important additional values which are reason why use of them for schema identifiers is considered a good practice. But about that I can write even more..