Bind parameters broken in 9.3?
my parameters look like this:
and date(t.created_at) >= ':DATETIME_M_from'
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.
Attempt to fix non functional bind parameter detection. See http://www.heidisql.com/forum.php?t=19171
* 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.
Is it possible to revert?
The log pane doesn't display any error message even with "Debug messages" enabled; no idea about how to gather further diagnose info.
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
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. */
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.
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?
- 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
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
* 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 ... :)
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.
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!
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.)
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).
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.
Sometimes you also need to quote numeric values, so I would not recommend changing this.
<?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, 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.
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.