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

Firebird .NET Data Provider reads Guids incorrectly on little-endian systems [DNET509] #493

Closed
firebird-automations opened this issue Jun 11, 2013 · 13 comments

Comments

@firebird-automations
Copy link

Submitted by: Herman Schoenfeld (sphere10.com)

On little-endian systems Guid values are not being correctly hydrated by the Firebird http://ADO.NET provider.

For example, a user may insert a Guid via a query of the form

..." INSERT INTO MyTable VALUES (UUID_TO_CHAR(" + guid.ToString() + " ))"

But when that user loads the Guid back via the provider (on a little endian system), the value is different than the correct value stored on the database value.

The problem is that Uuid & Guid vary in storage format but not it presentation format.The The CHAR(16) octets contain the Uuid bytes in big endian layout. When hydrating these bytes into a Guid, the provider does not compensate for the Guid's storage of certain parts as the native endian. The correct way to parse the uuid into a guid is:

		var rfc4122bytes =  \.\.\. uuid byte array returned from firebird \.\.
		if \(BitConverter\.IsLittleEndian\) \{
			Array\.Reverse\(rfc4122bytes, 0, 4\);
			Array\.Reverse\(rfc4122bytes, 4, 2\);
			Array\.Reverse\(rfc4122bytes, 6, 2\);
		\}
		var guid = new Guid\(rfc4122bytes\);

Suspected location of bug is in class 'DbValue' method 'GetGuid'.

As a work-around, I've written a wrapper for the FbDataReader which will correct the invalid guid value.

public class FirebirdCorrectingReader : IDataReader \{
	private readonly IDataReader \_decoratedReader;

	public FirebirdCorrectingReader\(IDataReader decoratedReader\) \{
		\_decoratedReader = decoratedReader;	
	\}

	#⁠region IDataReader Impl

	public void Dispose\(\) \{
		\_decoratedReader\.Dispose\(\);
	\}

	public string GetName\(int i\) \{
		return \_decoratedReader\.GetName\(i\);
	\}

	public string GetDataTypeName\(int i\) \{
		return \_decoratedReader\.GetDataTypeName\(i\);
	\}

	public Type GetFieldType\(int i\) \{
		return \_decoratedReader\.GetFieldType\(i\);
	\}

	public object GetValue\(int i\) \{
		var result = \_decoratedReader\.GetValue\(i\);
		if \(result is Guid\) \{
			result = CorrectGuid\(\(Guid\)result\);
		\}
		return result;
	\}

	public int GetValues\(object\[\] values\) \{
		return \_decoratedReader\.GetValues\(values\);
	\}

	public int GetOrdinal\(string name\) \{
		return \_decoratedReader\.GetOrdinal\(name\);
	\}

	public bool GetBoolean\(int i\) \{
		return \_decoratedReader\.GetBoolean\(i\);
	\}

	public byte GetByte\(int i\) \{
		return \_decoratedReader\.GetByte\(i\);
	\}

	public long GetBytes\(int i, long fieldOffset, byte\[\] buffer, int bufferoffset, int length\) \{
		return \_decoratedReader\.GetBytes\(i, fieldOffset, buffer, bufferoffset, length\);
	\}

	public char GetChar\(int i\) \{
		return \_decoratedReader\.GetChar\(i\);
	\}

	public long GetChars\(int i, long fieldoffset, char\[\] buffer, int bufferoffset, int length\) \{
		return \_decoratedReader\.GetChars\(i, fieldoffset, buffer, bufferoffset, length\);
	\}

	public Guid GetGuid\(int i\) \{
		return CorrectGuid\(\_decoratedReader\.GetGuid\(i\)\);
	\}

	public short GetInt16\(int i\) \{
		return \_decoratedReader\.GetInt16\(i\);
	\}

	public int GetInt32\(int i\) \{
		return \_decoratedReader\.GetInt32\(i\);
	\}

	public long GetInt64\(int i\) \{
		return \_decoratedReader\.GetInt64\(i\);
	\}

	public float GetFloat\(int i\) \{
		return \_decoratedReader\.GetFloat\(i\);
	\}

	public double GetDouble\(int i\) \{
		return \_decoratedReader\.GetDouble\(i\);
	\}

	public string GetString\(int i\) \{
		return \_decoratedReader\.GetString\(i\);
	\}

	public decimal GetDecimal\(int i\) \{
		return \_decoratedReader\.GetDecimal\(i\);
	\}

	public DateTime GetDateTime\(int i\) \{
		return \_decoratedReader\.GetDateTime\(i\);
	\}

	public IDataReader GetData\(int i\) \{
		return \_decoratedReader\.GetData\(i\);
	\}

	public bool IsDBNull\(int i\) \{
		return \_decoratedReader\.IsDBNull\(i\);
	\}

	public int FieldCount \{ get \{ return \_decoratedReader\.FieldCount; \} \}

	object IDataRecord\.this\[int i\] \{
		get \{ return \_decoratedReader\[i\]; \}
	\}

	object IDataRecord\.this\[string name\] \{
		get \{return \_decoratedReader\[name\]; \}
	\}

	public void Close\(\) \{
		\_decoratedReader\.Close\(\);
	\}

	public DataTable GetSchemaTable\(\) \{
		return \_decoratedReader\.GetSchemaTable\(\);
	\}

	public bool NextResult\(\) \{
		return \_decoratedReader\.NextResult\(\);
	\}

	public bool Read\(\) \{
		return \_decoratedReader\.Read\(\);
	\}

	public int Depth \{ get \{ return \_decoratedReader\.Depth; \} \}
	public bool IsClosed \{ get \{ return \_decoratedReader\.IsClosed; \} \}
	public int RecordsAffected \{ get \{ return \_decoratedReader\.RecordsAffected; \} \}

	#⁠endregion

	public static Guid CorrectGuid\(Guid badlyParsedGuid\) \{
		var rfc4122bytes = badlyParsedGuid\.ToByteArray\(\);
		if \(BitConverter\.IsLittleEndian\) \{
			Array\.Reverse\(rfc4122bytes, 0, 4\);
			Array\.Reverse\(rfc4122bytes, 4, 2\);
			Array\.Reverse\(rfc4122bytes, 6, 2\);
		\}
		return new Guid\(rfc4122bytes\);
	\}
\}

Commits: fdb00e0

@firebird-automations
Copy link
Author

Commented by: Herman Schoenfeld (sphere10.com)

Fixed typos.

@firebird-automations
Copy link
Author

Modified by: Herman Schoenfeld (sphere10.com)

description: On a little-endian systems Guids values are not being correctly hydrated by the Firebird http://ADO.NET provider.

For example, a user may insert a Guid via a query of the form

..." INSERT INTO MyTable VALUES (UUID_TO_CHAR(" + guid.ToString() + " ))"

But when that user loads the Guid back via the provider (on a little endian system), the value is different than the correct value stored on the database value.

The problem is that Uuid & Guid vary in storage format but not it presentation format.The The CHAR(16) octets contain the Uuid bytes in big endian layout. When hydrating these bytes into a Guid, the provider does not compensate for the Guid's storage of certain parts as the native endian. The correct way to parse the uuid into a guid is:

		var rfc4122bytes =  \.\.\. uuid byte array returned from firebird \.\.
		if \(BitConverter\.IsLittleEndian\) \{
			Array\.Reverse\(rfc4122bytes, 0, 4\);
			Array\.Reverse\(rfc4122bytes, 4, 2\);
			Array\.Reverse\(rfc4122bytes, 6, 2\);
		\}
		var guid = new Guid\(rfc4122bytes\);

Suspected location of bug is in class 'DbValue' method 'GetGuid'.

As a work-around, I've written a wrapper for the FbDataReader which will correct the invalid guid value.

public class FirebirdCorrectingReader : IDataReader \{
	private readonly IDataReader \_decoratedReader;

	public FirebirdCorrectingReader\(IDataReader decoratedReader\) \{
		\_decoratedReader = decoratedReader;	
	\}

	#⁠region IDataReader Impl

	public void Dispose\(\) \{
		\_decoratedReader\.Dispose\(\);
	\}

	public string GetName\(int i\) \{
		return \_decoratedReader\.GetName\(i\);
	\}

	public string GetDataTypeName\(int i\) \{
		return \_decoratedReader\.GetDataTypeName\(i\);
	\}

	public Type GetFieldType\(int i\) \{
		return \_decoratedReader\.GetFieldType\(i\);
	\}

	public object GetValue\(int i\) \{
		var result = \_decoratedReader\.GetValue\(i\);
		if \(result is Guid\) \{
			result = CorrectGuid\(\(Guid\)result\);
		\}
		return result;
	\}

	public int GetValues\(object\[\] values\) \{
		return \_decoratedReader\.GetValues\(values\);
	\}

	public int GetOrdinal\(string name\) \{
		return \_decoratedReader\.GetOrdinal\(name\);
	\}

	public bool GetBoolean\(int i\) \{
		return \_decoratedReader\.GetBoolean\(i\);
	\}

	public byte GetByte\(int i\) \{
		return \_decoratedReader\.GetByte\(i\);
	\}

	public long GetBytes\(int i, long fieldOffset, byte\[\] buffer, int bufferoffset, int length\) \{
		return \_decoratedReader\.GetBytes\(i, fieldOffset, buffer, bufferoffset, length\);
	\}

	public char GetChar\(int i\) \{
		return \_decoratedReader\.GetChar\(i\);
	\}

	public long GetChars\(int i, long fieldoffset, char\[\] buffer, int bufferoffset, int length\) \{
		return \_decoratedReader\.GetChars\(i, fieldoffset, buffer, bufferoffset, length\);
	\}

	public Guid GetGuid\(int i\) \{
		return CorrectGuid\(\_decoratedReader\.GetGuid\(i\)\);
	\}

	public short GetInt16\(int i\) \{
		return \_decoratedReader\.GetInt16\(i\);
	\}

	public int GetInt32\(int i\) \{
		return \_decoratedReader\.GetInt32\(i\);
	\}

	public long GetInt64\(int i\) \{
		return \_decoratedReader\.GetInt64\(i\);
	\}

	public float GetFloat\(int i\) \{
		return \_decoratedReader\.GetFloat\(i\);
	\}

	public double GetDouble\(int i\) \{
		return \_decoratedReader\.GetDouble\(i\);
	\}

	public string GetString\(int i\) \{
		return \_decoratedReader\.GetString\(i\);
	\}

	public decimal GetDecimal\(int i\) \{
		return \_decoratedReader\.GetDecimal\(i\);
	\}

	public DateTime GetDateTime\(int i\) \{
		return \_decoratedReader\.GetDateTime\(i\);
	\}

	public IDataReader GetData\(int i\) \{
		return \_decoratedReader\.GetData\(i\);
	\}

	public bool IsDBNull\(int i\) \{
		return \_decoratedReader\.IsDBNull\(i\);
	\}

	public int FieldCount \{ get \{ return \_decoratedReader\.FieldCount; \} \}

	object IDataRecord\.this\[int i\] \{
		get \{ return \_decoratedReader\[i\]; \}
	\}

	object IDataRecord\.this\[string name\] \{
		get \{return \_decoratedReader\[name\]; \}
	\}

	public void Close\(\) \{
		\_decoratedReader\.Close\(\);
	\}

	public DataTable GetSchemaTable\(\) \{
		return \_decoratedReader\.GetSchemaTable\(\);
	\}

	public bool NextResult\(\) \{
		return \_decoratedReader\.NextResult\(\);
	\}

	public bool Read\(\) \{
		return \_decoratedReader\.Read\(\);
	\}

	public int Depth \{ get \{ return \_decoratedReader\.Depth; \} \}
	public bool IsClosed \{ get \{ return \_decoratedReader\.IsClosed; \} \}
	public int RecordsAffected \{ get \{ return \_decoratedReader\.RecordsAffected; \} \}

	#⁠endregion

	public static Guid CorrectGuid\(Guid badlyParsedGuid\) \{
		var rfc4122bytes = badlyParsedGuid\.ToByteArray\(\);
		if \(BitConverter\.IsLittleEndian\) \{
			Array\.Reverse\(rfc4122bytes, 0, 4\);
			Array\.Reverse\(rfc4122bytes, 4, 2\);
			Array\.Reverse\(rfc4122bytes, 6, 2\);
		\}
		return new Guid\(rfc4122bytes\);
	\}
\}

=>

On little-endian systems Guid values are not being correctly hydrated by the Firebird http://ADO.NET provider.

For example, a user may insert a Guid via a query of the form

..." INSERT INTO MyTable VALUES (UUID_TO_CHAR(" + guid.ToString() + " ))"

But when that user loads the Guid back via the provider (on a little endian system), the value is different than the correct value stored on the database value.

The problem is that Uuid & Guid vary in storage format but not it presentation format.The The CHAR(16) octets contain the Uuid bytes in big endian layout. When hydrating these bytes into a Guid, the provider does not compensate for the Guid's storage of certain parts as the native endian. The correct way to parse the uuid into a guid is:

		var rfc4122bytes =  \.\.\. uuid byte array returned from firebird \.\.
		if \(BitConverter\.IsLittleEndian\) \{
			Array\.Reverse\(rfc4122bytes, 0, 4\);
			Array\.Reverse\(rfc4122bytes, 4, 2\);
			Array\.Reverse\(rfc4122bytes, 6, 2\);
		\}
		var guid = new Guid\(rfc4122bytes\);

Suspected location of bug is in class 'DbValue' method 'GetGuid'.

As a work-around, I've written a wrapper for the FbDataReader which will correct the invalid guid value.

public class FirebirdCorrectingReader : IDataReader \{
	private readonly IDataReader \_decoratedReader;

	public FirebirdCorrectingReader\(IDataReader decoratedReader\) \{
		\_decoratedReader = decoratedReader;	
	\}

	#⁠region IDataReader Impl

	public void Dispose\(\) \{
		\_decoratedReader\.Dispose\(\);
	\}

	public string GetName\(int i\) \{
		return \_decoratedReader\.GetName\(i\);
	\}

	public string GetDataTypeName\(int i\) \{
		return \_decoratedReader\.GetDataTypeName\(i\);
	\}

	public Type GetFieldType\(int i\) \{
		return \_decoratedReader\.GetFieldType\(i\);
	\}

	public object GetValue\(int i\) \{
		var result = \_decoratedReader\.GetValue\(i\);
		if \(result is Guid\) \{
			result = CorrectGuid\(\(Guid\)result\);
		\}
		return result;
	\}

	public int GetValues\(object\[\] values\) \{
		return \_decoratedReader\.GetValues\(values\);
	\}

	public int GetOrdinal\(string name\) \{
		return \_decoratedReader\.GetOrdinal\(name\);
	\}

	public bool GetBoolean\(int i\) \{
		return \_decoratedReader\.GetBoolean\(i\);
	\}

	public byte GetByte\(int i\) \{
		return \_decoratedReader\.GetByte\(i\);
	\}

	public long GetBytes\(int i, long fieldOffset, byte\[\] buffer, int bufferoffset, int length\) \{
		return \_decoratedReader\.GetBytes\(i, fieldOffset, buffer, bufferoffset, length\);
	\}

	public char GetChar\(int i\) \{
		return \_decoratedReader\.GetChar\(i\);
	\}

	public long GetChars\(int i, long fieldoffset, char\[\] buffer, int bufferoffset, int length\) \{
		return \_decoratedReader\.GetChars\(i, fieldoffset, buffer, bufferoffset, length\);
	\}

	public Guid GetGuid\(int i\) \{
		return CorrectGuid\(\_decoratedReader\.GetGuid\(i\)\);
	\}

	public short GetInt16\(int i\) \{
		return \_decoratedReader\.GetInt16\(i\);
	\}

	public int GetInt32\(int i\) \{
		return \_decoratedReader\.GetInt32\(i\);
	\}

	public long GetInt64\(int i\) \{
		return \_decoratedReader\.GetInt64\(i\);
	\}

	public float GetFloat\(int i\) \{
		return \_decoratedReader\.GetFloat\(i\);
	\}

	public double GetDouble\(int i\) \{
		return \_decoratedReader\.GetDouble\(i\);
	\}

	public string GetString\(int i\) \{
		return \_decoratedReader\.GetString\(i\);
	\}

	public decimal GetDecimal\(int i\) \{
		return \_decoratedReader\.GetDecimal\(i\);
	\}

	public DateTime GetDateTime\(int i\) \{
		return \_decoratedReader\.GetDateTime\(i\);
	\}

	public IDataReader GetData\(int i\) \{
		return \_decoratedReader\.GetData\(i\);
	\}

	public bool IsDBNull\(int i\) \{
		return \_decoratedReader\.IsDBNull\(i\);
	\}

	public int FieldCount \{ get \{ return \_decoratedReader\.FieldCount; \} \}

	object IDataRecord\.this\[int i\] \{
		get \{ return \_decoratedReader\[i\]; \}
	\}

	object IDataRecord\.this\[string name\] \{
		get \{return \_decoratedReader\[name\]; \}
	\}

	public void Close\(\) \{
		\_decoratedReader\.Close\(\);
	\}

	public DataTable GetSchemaTable\(\) \{
		return \_decoratedReader\.GetSchemaTable\(\);
	\}

	public bool NextResult\(\) \{
		return \_decoratedReader\.NextResult\(\);
	\}

	public bool Read\(\) \{
		return \_decoratedReader\.Read\(\);
	\}

	public int Depth \{ get \{ return \_decoratedReader\.Depth; \} \}
	public bool IsClosed \{ get \{ return \_decoratedReader\.IsClosed; \} \}
	public int RecordsAffected \{ get \{ return \_decoratedReader\.RecordsAffected; \} \}

	#⁠endregion

	public static Guid CorrectGuid\(Guid badlyParsedGuid\) \{
		var rfc4122bytes = badlyParsedGuid\.ToByteArray\(\);
		if \(BitConverter\.IsLittleEndian\) \{
			Array\.Reverse\(rfc4122bytes, 0, 4\);
			Array\.Reverse\(rfc4122bytes, 4, 2\);
			Array\.Reverse\(rfc4122bytes, 6, 2\);
		\}
		return new Guid\(rfc4122bytes\);
	\}
\}

@firebird-automations
Copy link
Author

Commented by: damjan (damjan)

Hi Jiri

Is there a plan to fix this in the future? Or at lease some workaround?

We have a char(16) field in the database table that is the ID of the entry.
When we save an entry in the database, the GUID is 391987b6-adb7-e511-8beb-80000b8906d3
and when we retrieve the data from the table, the GUID in the retrieved object is b6871939-b7ad-11e5-8beb-80000b8906d3

Is there a solution that would not imply converting the GUID after retrieval? Maybe using a different type of ID field in the database? Or downloading a different version of the provider?

Our Firebird client version is 4.6.1.0

Thank you

Damjan

@firebird-automations
Copy link
Author

Commented by: @cincuranet

The problem is that FirebirdClient had GUID support before Firebird and hence it was not clear how the binary data will be interpreted by Firebird (if ever, at that time). Changing this currently is a huge breaking change. That's why I'm kind of slow doing it. And also because when you store GUID from .NET and read it again from .NET the string representation is the same. So the only problem is mixing UUID_TO_CHAR (and inverse function) values and string values from .NET.

Kind of wish there was a way to change this without breaking anything (or introducing API discrepancies).

@firebird-automations
Copy link
Author

Commented by: damjan (damjan)

The problem is the following:

guid1 is original guid, guid2 is the one saved in DB

1. create entity with guid1
2. save to DB (entity saved with guid2)
3. retrieve all entities from DB to memory (entity read to memory with guid1) - until here is OK
4. retrieve entity from DB where guid=guid1 (taken from the retrieved list) - FAILS

At point 4 we use a typical linq query with entity framework entities. Maybe the Firebird Entity framework provider doesn't take in account this particularity of changed guids, in the moment of materializing the "where" query.

It doesn't happen with TSource commands like "FirstOrDefault" or "SingleOrDefault". Only with IQueryable like "Where". Maybe the problem lays in the Firebird Entity Framework Provider and not in Firebird http://Ado.Net Provider.

We use Firebird Entity Framework Provider 4.6.1.0 and Firebird http://Ado.Net Provider 4.6.1.0.

We would appreciate any suggestion on how to deal with that.

@firebird-automations
Copy link
Author

Commented by: damjan (damjan)

Hi again Jiri

We have now more specific description of our problem.

We are trying to dynamically create a query according to some property name and value metadata, but we are not retrieving the correct entities.

Using normal LINQ query works as expected, and the generated SQL Query contains a parameter without the CHAR_TO_UUID function:
result.Where(e => e.OId == (Guid)value);

SQL Query: WHERE "V"."LE_OID" = @p__linq__0) AS "B"

Using a System.Linq.Expression the query contains the UUID function call with the GUID and returns no results:
WHERE CHAR_TO_UUID('9c4f4c13-e8c4-e511-a075-c4d987d9ee54') = "E"."LE_OID"

Here is a code snippet to create the Expression:
PropertyInfo prop = typeof(myEntity).GetProperty(fieldName);
if (prop != null)
{
result = result.Where(PropertyEquals<myEntity, Guid>(prop, (Guid)value));
}

public static Expression<Func<TItem, bool>> PropertyEquals<TItem, TValue>(
PropertyInfo property, TValue value)
{
var param = Expression.Parameter(typeof(TItem));
var body = Expression.Equal(Expression.Property(param, property),
Expression.Constant(value));
return Expression.Lambda<Func<TItem, bool>>(body, param);
}

Is it possible to generate a sql query like the first with a expression or is there any other solution to achieve this goal?

Thank you

@firebird-automations
Copy link
Author

Commented by: @cincuranet

@herman Schoenfeld:
You're inserting the value as string, while the GUID is opaque value. You should insert it with parameter. Then it will match.

@Damjan:
That's because of your Expression.Constant. Use it as variable and it will use parameter.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

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

@firebird-automations
Copy link
Author

@firebird-automations
Copy link
Author

Commented by: @cincuranet

Can you maybe any of you test this https://ci.appveyor.com/project/cincura_net/firebirdsql-data-firebirdclient/build/1171/job/ix1hjw5v8tg9hhuu/artifacts build. It should have the new Guid reading/writing.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Fix Version: vNextBig [ 10850 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

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

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

summary: Firebird .NET Data Provider reads Guids incorrectly on little-endian systems => Firebird .NET Data Provider reads Guids incorrectly on little-endian systems #⁠breaking

@cincuranet cincuranet changed the title Firebird .NET Data Provider reads Guids incorrectly on little-endian systems #breaking [DNET509] Firebird .NET Data Provider reads Guids incorrectly on little-endian systems [DNET509] May 26, 2021
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