| Bug #48612 | Prepared statements ~ string and binary data types indistinguishable | ||
|---|---|---|---|
| Submitted: | 7 Nov 2009 13:11 | Modified: | 27 Aug 2020 18:31 |
| Reporter: | Guillermo Prandi | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Prepared statements | Severity: | S2 (Serious) |
| Version: | 5.1.40 | OS: | Any (Tested on Windows, embedded server) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | binary data, prepared statements, string data | ||
[9 Nov 2009 8:19]
Tonci Grgin
Hi Guillermo and thanks for your report. This is nothing new so I'll have to ask you to check on Bug#10491 [20 Mar 2007 10:48] Tonci Grgin This problem represents grave issue for connectors. They can't work around it, at least not reliably. Ad hoc user queries like this do not allow connector to distinguish between "SHOW CREATE TABLE", where it should treat BINARY as UTF8, and "SELECT varbinary_col FROM some_table", where it really should be binary... then Bug#27790, Bug#28113, Bug#28306, Bug#28420, Bug#16291 and so on... As for "how does c/NET do it" it's a question of connection parameters where you can choose to *ignore* the problem you described and just treat all function results as text. So it's not really something magical but rather a workaround that exists in all of connectors.
[10 Nov 2009 11:26]
Valeriy Kravchuk
I can confirm the problem:
openxs@suse:/home2/openxs/dbs/5.1> gcc -o 48612 `$CFG --cflags` 48612.c `$CFG --libs`
openxs@suse:/home2/openxs/dbs/5.1> ./48612
Client info: 5.1.41
Server info: 5.1.41-debug
type=12
length=19
decimals=6
flags=129
type=253
length=0
decimals=0
flags=128
type=253
length=16
decimals=31
flags=129
There is no way to fistinguish f2 and f3. Actually, metadata for the result can be different, depending on value binded, and we have no good way to predic them. Look, collation differs for example:
openxs@suse:/home2/openxs/dbs/5.1> bin/mysql -uroot --column-type-info test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.1.41-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> prepare stmt from "select now() as f1, ? as f2, aes_decrypt('abc', 'x') as f3";
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> execute stmt using @d
-> ;
Field 1: `f1`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: DATETIME
Collation: binary (63)
Length: 19
Max_length: 19
Decimals: 6
Flags: NOT_NULL BINARY
Field 2: `f2`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 0
Max_length: 0
Decimals: 0
Flags: BINARY
Field 3: `f3`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 3
Max_length: 0
Decimals: 31
Flags: BINARY
+---------------------+------+------+
| f1 | f2 | f3 |
+---------------------+------+------+
| 2010-02-15 19:53:08 | NULL | NULL |
+---------------------+------+------+
1 row in set (0.00 sec)
mysql> set @d='2009-11-05 17:00:00';
Query OK, 0 rows affected (0.00 sec)
mysql> execute stmt using @d;
Field 1: `f1`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: DATETIME
Collation: binary (63)
Length: 19
Max_length: 19
Decimals: 6
Flags: NOT_NULL BINARY
Field 2: `f2`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: latin1_swedish_ci (8)
Length: 19
Max_length: 19
Decimals: 0
Flags: NOT_NULL
Field 3: `f3`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 3
Max_length: 0
Decimals: 31
Flags: BINARY
+---------------------+---------------------+------+
| f1 | f2 | f3 |
+---------------------+---------------------+------+
| 2010-02-15 19:53:27 | 2009-11-05 17:00:00 | NULL |
+---------------------+---------------------+------+
1 row in set (0.00 sec)
So, I'd say this should be just properly explained in the manual.
[10 Nov 2009 11:27]
Valeriy Kravchuk
Sample test case used
Attachment: 48612.c (text/x-csrc), 1.52 KiB.
[22 Dec 2009 18:37]
Konstantin Osipov
For a placeholder, the server can't not the type of the parameter, since it's the one specified by the user.
The type returned at prepare is a stub - MYSQL_TYPE_VAR_STRING is returned in all cases.
One should re-check metadata after execution, it will contain the proper type, as supplied by the user.
E.g. if I do:
mysql_stmt_prepare("select ?");
then
mysql_stmt_result_metadata();
-> I get MYSQL_TYPE_VAR_STRING for the column. It's a stub.
Then I do:
bind.type= MYSQL_TYPE_DATE;
mysql_stmt_bind_param();
mysql_stmt_execute();
mysql_stmt_result_metadata();
-> the type of the result set column is MYSQL_TYPE_DATE now.
Valeriy's verification approach is not applicable since it's using text protocol, in which all columns have string type.
[27 Aug 2020 18:31]
Paul DuBois
Posted by developer: Fixed in 8.0.22. For prepared statement parameters for which no contextual information is available to determine the parameter type, the server assumes the parameter is a character string with the default character set, not a binary string. Parameters for which this is incorrect may be placed within a CAST() expression.
Description: Hi. I'm facing this problem with the embedded server. It seems like a bug in the API interface design, or perhaps it is a bug in the documentation: there is no way to tell apart certain string data from binary data in perpared statements. Take this for example: SELECT now() as f1, ? as f2, aes_encrypt('abc','x') as f3; After preparing the statement, metadata from mysql_stmt_result_metadata() says: f1: type=MYSQL_TYPE_DATETIME, charsetnr=63, flags=BINARY_FLAG f2: type=MYSQL_TYPE_VAR_STRING, charsetnr=63, flags=BINARY_FLAG, def_length=2779096485, max_length=0, length=0, decimals=0 f3: type=MYSQL_TYPE_VAR_STRING, charsetnr=63, flags=BINARY_FLAG, def_length=2779096485, max_length=0, length=65535, decimals=31 Parameter '?' is then be bound to a MYSQL_TYPE_DATETIME buffer (value 2009-11-05 17:32:35) and the statement executed. The result set comes with the following: f1: expected MYSQL_TYPE_DATETIME from function now() f2: An ASCII string with '2009-11-05 17:32:35' f3: The binary data returned by the AES function. Given f2 is actually text (which in my case I need to convert to ucs-2) and f3 is opaque binary data, the API does not give me a way to tell them apart. The only differences I see are the max_length and decimals fields, which seem unrelated to the issue. This should not be a problem if I'd know the prepared statement in advance, but in a tool like 'mysql', where arbitrary statements are expected, it is impossible to know which returned fields will be string and which ones will be binary without asking libmysqld. This would also be non-issue if I didn't have to use ucs-2 (UTF-16) Unicode for strings, but Windows COM/.NET need all strings in ucs-2. Somehow the .Net/Connector knows the difference and overcomes the problem, since text is returned as String, whilst actual binary data is returned as Byte(), but I couldn't follow the code to track how it does it. How to repeat: See example above. Suggested fix: Actual binary data should come with MYSQL_TYPE_BINARY instead of MYSQL_TYPE_VARSTRING, although this looks like it could be a limitation of the API inferface, as result types are reported after parsing the statement but before the statement is executed, where the real data types are available. Another way to do this could be some way of post-querying the actual data type after the mysql_stmt_execute() call.