Bind parameters broken in 9.3?

eos's profile image eos posted 9 years ago in General Permalink
Hi, since I updated, bind parameters do not work - they are simply not recognized. If I tick the "Bind parameters" box on the right, there is no dropdown to edit the bind parameters. This is catastrophic!

my parameters look like this:

and date(t.created_at) >= ':DATETIME_M_from'

ansgar's profile image ansgar posted 9 years ago Permalink
I could just reproduce this issue, but after restarting Heidi I couldn't any longer. Hm...
eos's profile image eos posted 9 years ago Permalink
Interesting. I have tried restarting Heidi many times, but the problem persists. I am connecting to mysql 5.6 running locally. I have also tried different sql scripts, same problem.
kalvaro's profile image kalvaro posted 9 years ago Permalink


my parameters look like this:

and date(t.created_at) >= ':DATETIME_M_from'



That's a string literal, you need to remove quotes:

and date(t.created_at) >= :DATETIME_M_from


Whatever, they are indeed broken. I never too the time to report it but they seem to have been broken for a while.
[expired user #9201]'s profile image [expired user #9201] posted 9 years ago Permalink
They did still work in 9.2
ansgar's profile image ansgar posted 9 years ago Permalink
I can't get parameters to not to work any longer. I had it one time, but I cannot recall what I did before and what was probably the cause of it. Now eacht time I start HeidiSQL, bind parameters are working...
Code modification/commit 27fb791 from ansgarbecker, 9 years ago, revision 9.3.0.4985
Attempt to fix non functional bind parameter detection. See http://www.heidisql.com/forum.php?t=19171
ansgar's profile image ansgar posted 9 years ago Permalink
r4985 fixes non working bind parameters for this special case:
* activate bind parameters
* load a file bigger than 1MB into the editor, causing an error message "file too large for checking bind parameters"
* clear editor
* activate bind parameters, again

Could be that this also fixes other cases. However, please report back if the problem persists for you.
eos's profile image eos posted 9 years ago Permalink
Sadly, bind parameters still completely broken for all cases (even in one line of sql) with 4985. No workaround found - this makes developing SQL very difficult, as we use bind parameters in all our statements. Additionally with this build, it is now not possible to drag the upper window bigger, it just drags the margin bigger, so you end up with a huge grey bar between the upper (sql) and lower (result) windows.
Is it possible to revert?
kalvaro's profile image kalvaro posted 9 years ago Permalink
It's still broken for me in r4985. Even after a fresh start, the "Bind parameters" subtree remains empty. If I click on it to enable it, I just get the little arrow removed.

The log pane doesn't display any error message even with "Debug messages" enabled; no idea about how to gather further diagnose info.
ansgar's profile image ansgar posted 9 years ago Permalink
eos, can you confirm what Ozymandias-X said above - that bind parameters worked in v9.2 ? If yes, I could probably track the issue down to some modification.
Code modification/commit 3d80918 from ansgarbecker, 9 years ago, revision 9.3.0.4986
Add some debug messages so we can probably track down the cause of non functional bind parameters. See http://www.heidisql.com/forum.php?t=19171
ansgar's profile image ansgar posted 9 years ago Permalink
r4986 adds a few debug messages, which can be activated via Tools > Preferences > Logging > Debug messages.

As I still cannot reproduce the whole issue here, it looks like this when I check "Bind parameters":
/* Bind parameters enabled */
/* Bind parameter detection... */
/* 0 bind parameters found. */
<typed some sql with a parameter...>
/* Bind parameter detection... */
/* 1 bind parameters found. */
eos's profile image eos posted 9 years ago Permalink
can we set a test case? e.g. i I just create one line of sql thus:

:hello

and check the bind parmas box, would you expect it to show hello in the tree below it?

I dont get this in 9.3
eos's profile image eos posted 9 years ago Permalink
@ansgar, How can I downgrade to 9.2 without losing my database profiles?
ansgar's profile image ansgar posted 9 years ago Permalink
You can just install v9.2 without losing anything. If you fear losing something, create a backup using File > "Export settings" before installing.

But it would be nice if you could update to the latest build on the v9.3 and check what the SQL log says when enabling debug messages.
ansgar's profile image ansgar posted 9 years ago Permalink
Ah, and yes, that test case works here, on my Windows 10 box. See attached picture.
1 attachment(s):
  • bind-params-hello
eos's profile image eos posted 9 years ago Permalink
Here is what we see
1 attachment(s):
  • bind
eos's profile image eos posted 9 years ago Permalink
How do we enable debug logging?
ansgar's profile image ansgar posted 9 years ago Permalink
Tools > Preferences > Logging > "Debug messages"
eos's profile image eos posted 9 years ago Permalink
I did that, but cant find where the debug messages are logged.
I assumed I do not need to check the "Write SQL log to file" box?
ansgar's profile image ansgar posted 9 years ago Permalink
I thought that was clear. Look at the very bottom.
1 attachment(s):
  • bind-params-hello2
Battlestar's profile image Battlestar posted 9 years ago Permalink
@ansgar, same problem here. I enabled the debug logging but it seems there is no logging when changing the checkbox state.

Win 7 64, HeidiSQL 9.3.0.4984
ansgar's profile image ansgar posted 9 years ago Permalink
Are there other debug messages in the log window?
Battlestar's profile image Battlestar posted 9 years ago Permalink
Yes there are.
1 attachment(s):
  • 2015-08-31_14-22-17_heidi
eos's profile image eos posted 9 years ago Permalink
Ok, nothing comes out at bottom when enable debug except lots of "ping server" messages. No errors when typing :hello, nor when checking the bind params box or opening it up.
eos's profile image eos posted 9 years ago Permalink
I just installed 9.2 4983, and same problem - no bind parameters
ansgar's profile image ansgar posted 9 years ago Permalink
If there's nothing in the log when you check the "bind parameters" checkbox, then the checkbox code does not work.

r4987 has two new fixes which probably solve this issue. I'm just blindly doing things here, as it all works for me anyway. Could someone please update to the latest build and check if it works now?
Battlestar's profile image Battlestar posted 9 years ago Permalink
hmmm that is indeed very strange. Now, after trying 4986, both versions 4986 and 4984 work.
Battlestar's profile image Battlestar posted 9 years ago Permalink
I think I've got it:

- Click the little "expand/collapse" icon, which then disappears because there is no parameter found yet
- enable the checkbox
- Type in some query with parameters
- The log shows "/* 1 bind parameters found. */" but you cannot access it because the expand button is not visible
ansgar's profile image ansgar posted 9 years ago Permalink
Ok, that's what I had in mind also. r4986 should fix that, does it?
Battlestar's profile image Battlestar posted 9 years ago Permalink
No, unfortunately not.
1 attachment(s):
  • 2015-08-31_15-28-59_heidi
Code modification/commit df1bdc8 from ansgarbecker, 9 years ago, revision 9.3.0.4988
Reset bind param tree node after checking its checkbox, so the plus/minus icon appears when the node has previously been expanded. Should fix this issue finally. See http://www.heidisql.com/forum.php?t=19171
ansgar's profile image ansgar posted 9 years ago Permalink
Finally, I found a reproduction recipe for the issue:
* click the plus symbol, so the symbol disappears
* check the checkbox
* write some parameter into the editor

Just committed r4988, which resets the plus/minus symbol to make it appear again when the checkbox is checked. Should do it. Hopefully ... :)
ansgar's profile image ansgar posted 9 years ago Permalink
Battlestar, thanks a lot. I just see your reproduction recipe was exactly what I now wrote againsmileI should have read that more precisely. However, thanks for all the quick updates!
eos's profile image eos posted 9 years ago Permalink
I just installed 4988 (interestingly, windows 8.1 "smart screen" said it was a risk and I should not install it - not had this with any other version).

The bad news is the issue is not fixed.

The good news is that the debug output now shows some signs of life:

/* Result #1 fetched. */
/* Bind parameters enabled */
/* Bind parameter detection... */
/* 1 bind parameters found. */


But still nothing in the bind parameters drop down.
1 attachment(s):
  • Capture
eos's profile image eos posted 9 years ago Permalink
Ok, just noticed something very interesting.

If I tick the "bind params" box, then quickly hit the little arrow to the left of it to open them up, no bind params :(

If I tick the "bind params" box, then wait a number of seconds, the list of bind params automatically opens its self up :)

We have a work around!
ansgar's profile image ansgar posted 9 years ago Permalink
Are you sure you are on r4988? Just have a look at the window title.
kalvaro's profile image kalvaro posted 9 years ago Permalink
It's finally working for me. However, this code:

select :hello


.... triggers queries like these:

select foo;
select foo bar;


i.e. param values aren't quoted properly, thus missing the point of using prepared statements.

(I also confirm eos' remark about quickly clicking the checkbox.)
ansgar's profile image ansgar posted 9 years ago Permalink
Bind parameters should be quoted, but they never were in HeidiSQL yet. So that's "normal" for now.

But the plus/minus symbol is now working as expected?
Battlestar's profile image Battlestar posted 9 years ago Permalink
I think it's still not a good solution, resetting the plus/minus button on checkbox change. It still fails when you enable the checkbox without writing any query, click the plus/minus button (i.e. hiding it) and then adding a parameter to the query.

Instead, just check if the state of the plus/minus visibility matches the existence of items in that node every time you look for parameters in the query (seems you've got a timer there).
ansgar's profile image ansgar posted 9 years ago Permalink
Sigh. New attempt: r4989. This should finally fix it all. Anyway in what order you check the checkbox, expand or collapse the node, write parameters into the editor.

Sorry for the mess here. That's a part of the code which is not entirely from me.
kalvaro's profile image kalvaro posted 9 years ago Permalink

Bind parameters should be quoted, but they never were in HeidiSQL yet. So that's "normal" for now.



Oh... It's clear I haven't really used the feature in the past :)

When you have some spare time:

select :hello, ':world'


... should offer only one parameter (:hello) and of course it'd have to be properly quoted. As an added bonus, you could not quote numeric values.

BTW, checkbox finally works fine for me at r4989.
eos's profile image eos posted 9 years ago Permalink
@Kalvaro, that's not how bind params work, unfortunately. In your example :hello and :world should be two bind parameters. Frequently you need to quote the bind param (e.g. for dates). In 20 years of using bind parameters in this way with Oracle and TOAD, I have not come across the case where I had to escape a : char because I needed it at the start of a string literal. If we do have an escape, it should be something more like this: '\:i am not a bind param', (not sure which char could be used here) but I don't think this is needed.
Sometimes you also need to quote numeric values, so I would not recommend changing this.
ansgar's profile image ansgar posted 9 years ago Permalink
What I can tell from PHP it's exactly as kalvaro said. An example shows such an SQL string:
<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>


Well, when you say Toad does it also do the same way than I guess the current way HeidiSQL does it is not entirely wrong.
kalvaro's profile image kalvaro posted 9 years ago Permalink

@Kalvaro, that's not how bind params work, unfortunately. In your example :hello and :world should be two bind parameters. Frequently you need to quote the bind param (e.g. for dates). In 20 years of using bind parameters in this way with Oracle and TOAD



I don't use TOAD but both Oracle SQL Developer and the OCI library itself (both of them official Oracle products) do it the way I suggested. The same happens with PHP libraries like PDO, MySQLi or SQLSRV. If you have to quote parameter values yourself, then prepared statements do not protect at all against SQL injection.
Battlestar's profile image Battlestar posted 9 years ago Permalink


I don't use TOAD but both Oracle SQL Developer and the OCI library itself (both of them official Oracle products) do it the way I suggested. The same happens with PHP libraries like PDO, MySQLi or SQLSRV. If you have to quote parameter values yourself, then prepared statements do not protect at all against SQL injection.



That's exactly what I was gonna say. But it's not only SQL injection, you could add a lot of useful features when you let the software do the quotation. You don't need to care about escaping special characters (consider mixed string qualifiers, i.e. single- and double quotes are allowed in MySQL for strings). How about a datepicker for datetime values?

Please login to leave a reply, or register at first.