Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Long VARCHAR parameters in where clause reported as "string truncation" [DNET124] #135

Closed
firebird-automations opened this issue Oct 24, 2007 · 26 comments

Comments

@firebird-automations
Copy link

Submitted by: St?phane Claret (_skip)

Is duplicated by DNET178
Relate to DNET328
Relate to DNET377
Relate to DNET420
Is duplicated by DNET618

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

Commits: d61e7d0 62ca79c

@firebird-automations
Copy link
Author

Commented by: @carlosga

Could you please tell use what is the database character set and what is the connection string character set ??

@firebird-automations
Copy link
Author

Commented by: St?phane Claret (_skip)

Of course,
My database uses character set ISO8859_1;
I also specify "CharSet=ISO8859_1" in my connection string when opening an FbConnection.

@firebird-automations
Copy link
Author

Modified by: @carlosga

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Author

Commented by: @carlosga

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 )

@firebird-automations
Copy link
Author

Modified by: @carlosga

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Author

Modified by: @carlosga

Fix Version: 2.5.0 [ 10170 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

assignee: Carlos Guzman Alvarez [ carlosga_fb ] => Jiri Cincura [ cincura_net ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Fix Version: 2.5.0 [ 10170 ] =>

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Author

Commented by: @cincuranet

Parameter handling reworked according to SqlClient behavior. When the value is longer than declared size, the value is truncated.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: In Progress [ 3 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5.0. Alpha 3 [ 10261 ]

Fix Version: 2.5.0 [ 10170 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Link: This issue is duplicated by DNET178 [ DNET178 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Fix Version: 2.5.0 [ 10170 ] =>

@firebird-automations
Copy link
Author

Commented by: @cincuranet

Reopened for better fix.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Author

Commented by: @cincuranet

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.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: Reopened [ 4 ] => Closed [ 6 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5.3 [ 10380 ]

Fix Version: 2.5.0 Beta 1 [ 10261 ] =>

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Link: This issue relate to DNET328 [ DNET328 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Fix Version: 2.6 [ 10371 ]

Fix Version: 2.5.3 [ 10380 ] =>

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Link: This issue relate to DNET377 [ DNET377 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

See CORE3559 ; 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.

@firebird-automations
Copy link
Author

Commented by: @cincuranet

You have to specify length explicitly and then you're fine.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Link: This issue is related to DNET420 [ DNET420 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Link: This issue is related to DNET420 [ DNET420 ] =>

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Link: This issue relate to DNET420 [ DNET420 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Link: This issue is duplicated by DNET618 [ DNET618 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants