Issue Details (XML | Word | Printable)

Key: DNET-124
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: Jiri Cincura
Reporter: St?phane Claret
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
.NET Data provider

Long VARCHAR parameters in where clause reported as "string truncation"

Created: 24/Oct/07 03:09 PM   Updated: 02/Jul/15 03:55 PM
Component/s: ADO.NET Provider
Affects Version/s: 2.0.1
Fix Version/s: 2.6

Environment: WIndows XP, firebird engine 2.01, visual studio 2005
Issue Links:
Duplicate
 
Relate
 


 Description  « Hide
Hello,

Let's consider this simple table :

-------------------------------------------
CREATE TABLE DEVISE (
       CODE_DEVISE Char(3) NOT NULL COLLATE ES_ES_CI_AI,
       NOM_DEVISE Varchar(20) NOT NULL COLLATE ES_ES_CI_AI,
       CONSTRAINT PK_DEVISE PRIMARY KEY (CODE_DEVISE)
);

INSERT INTO DEVISE (code_devise, nom_devise) VALUES (chg, "test");
-------------------------------------------

Now if you try to execute the following code in a c# program

--------------------------------------------
FbCommand fb = conex.CreateCommand();
fb.CommandText = "SELECT * FROM DEVISE WHERE DEVISE.CODE_DEVISE LIKE 'chg%'";

FbDataReader reader = fb.ExecuteReader();
reader.Read();
--------------------------------------------

It works perfectly, (1 row returned )


But now, if you try to execute the same sql statement but by using a parameter in the WHERE Clause instead of a constant value :

--------------------------------------------
FbCommand fb = conex.CreateCommand();

fb.CommandText = "SELECT * FROM DEVISE WHERE DEVISE.CODE_DEVISE LIKE @devise1";
fb.Parameters.Add("@devise1", "chg%");

FbDataReader reader = fb.ExecuteReader();
reader.Read();
--------------------------------------------

fb.ExecuteReader raises a numeric overflow / String truncation exception, most probably because the length of "chg%" (4 chars) exceeds the size of the code_devise column ( CHAR(3) ).
I think that the lenght of string parameters should not be so strictly enforced when they are used in a WHERE clause because it produces unwanted behavior and prevent execution of statements which are actually correct.

Regards


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Carlos Guzman Alvarez added a comment - 25/Oct/07 12:19 PM
Could you please tell use what is the database character set and what is the connection string character set ??

St?phane Claret added a comment - 25/Oct/07 01:25 PM - edited
Of course,
My database uses character set ISO8859_1;
I also specify "CharSet=ISO8859_1" in my connection string when opening an FbConnection.

Carlos Guzman Alvarez made changes - 02/Nov/07 07:42 AM
Field Original Value New Value
Status Open [ 1 ] In Progress [ 3 ]
Carlos Guzman Alvarez added a comment - 03/Nov/07 06:45 AM
Ok, the problem relies on the provider being checking parameter value length against the parameter max length ( returned by the server in the case )

The only way to solve this is to remove that checking and let the server doing that but i'm not sure wheter that is a good idea or not ( because it may have side effects )

Carlos Guzman Alvarez made changes - 03/Nov/07 06:46 AM
Status In Progress [ 3 ] Open [ 1 ]
Carlos Guzman Alvarez made changes - 03/Nov/07 06:48 AM
Fix Version/s 2.5.0 [ 10170 ]
Jiri Cincura made changes - 17/Feb/08 08:30 AM
Assignee Carlos Guzman Alvarez [ carlosga_fb ] Jiri Cincura [ cincura_net ]
Jiri Cincura made changes - 06/Mar/08 07:49 AM
Fix Version/s 2.5.0 [ 10170 ]
Jiri Cincura made changes - 18/May/08 04:05 PM
Status Open [ 1 ] In Progress [ 3 ]
Jiri Cincura added a comment - 07/Sep/08 03:48 PM
Parameter handling reworked according to SqlClient behavior. When the value is longer than declared size, the value is truncated.

Jiri Cincura made changes - 07/Sep/08 03:48 PM
Fix Version/s 2.5.0. Alpha 3 [ 10261 ]
Fix Version/s 2.5.0 [ 10170 ]
Resolution Fixed [ 1 ]
Status In Progress [ 3 ] Resolved [ 5 ]
Jiri Cincura made changes - 03/Oct/08 02:13 PM
Link This issue is duplicated by DNET-178 [ DNET-178 ]
Jiri Cincura made changes - 07/Feb/09 11:58 AM
Fix Version/s 2.5.0 [ 10170 ]
Jiri Cincura added a comment - 30/Jun/10 07:48 PM
Reopened for better fix.

Jiri Cincura made changes - 30/Jun/10 07:48 PM
Resolution Fixed [ 1 ]
Status Resolved [ 5 ] Reopened [ 4 ]
Jiri Cincura added a comment - 30/Jun/10 07:52 PM
I was not happy with the behavior here as it looked weird to me. I dig little bit deeper into it and found clean and appropriate solution (also adheres with SqlClient's behavior).

If you specify the length of parameter it's used as long as it's less than or equal to column/expression you're using on the other side. The value, if string, is right truncated to this length.

Else the value is left to processing of server/protocol. MSSQL eats it longer and processes. But not Firebird as you can see with this (server & using params) "set term !; execute block as begin execute statement ('select * from VarcharTruncTest where s like ?') ('%aaa'); end! set term ;!". That also means FirebirdClient will throw exception. Solution is to preprocess the value by yourself on client side or let the server expect properly sized parameters i.e. via cast.

Jiri Cincura made changes - 30/Jun/10 07:52 PM
Status Reopened [ 4 ] Closed [ 6 ]
Fix Version/s 2.5.3 [ 10380 ]
Fix Version/s 2.5.0 Beta 1 [ 10261 ]
Resolution Fixed [ 1 ]
Jiri Cincura made changes - 30/Jun/10 07:53 PM
Link This issue relate to DNET-328 [ DNET-328 ]
Jiri Cincura made changes - 17/Nov/10 07:45 AM
Fix Version/s 2.6 [ 10371 ]
Fix Version/s 2.5.3 [ 10380 ]
Jiri Cincura made changes - 09/Apr/11 06:27 PM
Link This issue relate to DNET-377 [ DNET-377 ]
Mark Rotteveel added a comment - 16/Jul/11 08:14 PM
See CORE-3559 ; As far as I can see the applied fix consists of truncating the parameter value. This can lead to undesirable behavior (eg: a pattern that should match no longer does, or a pattern which should not match now does match). And especially valid patterns with a longer lenght (eg containing escapes) do not work.

Jiri Cincura added a comment - 17/Jul/11 04:27 PM
You have to specify length explicitly and then you're fine.

Jiri Cincura made changes - 01/Aug/12 10:48 AM
Link This issue is related to DNET-420 [ DNET-420 ]
Jiri Cincura made changes - 01/Aug/12 10:49 AM
Link This issue is related to DNET-420 [ DNET-420 ]
Jiri Cincura made changes - 01/Aug/12 10:49 AM
Link This issue relate to DNET-420 [ DNET-420 ]
Jiri Cincura made changes - 02/Jul/15 03:55 PM
Link This issue is duplicated by DNET-618 [ DNET-618 ]