[SOLVED] Help - string is being trimmed automatically by Mys
Moderator: General Moderators
Help - string is being trimmed automatically by Mysql
I'm having an issue with Mysql trimming a space character off the string data being inserted into my database. The field type is "varchar".
What I'm doing is encrypting a string and inserting it. The problem is that MCrypt sometimes uses a space as a part of the encryption string. If that space is at the end (probably the beginning too), my decryption fails because mysql trimmed the space off.
I can't seem to find any settings to turn this auto-trim off. Is this possible? How can I force Mysql to keep the end spaces?
I can solve this by testing the decryption, if it fails - add a space and try again. But, I'd like to solve this the right way.
Any help would be appreciated!
What I'm doing is encrypting a string and inserting it. The problem is that MCrypt sometimes uses a space as a part of the encryption string. If that space is at the end (probably the beginning too), my decryption fails because mysql trimmed the space off.
I can't seem to find any settings to turn this auto-trim off. Is this possible? How can I force Mysql to keep the end spaces?
I can solve this by testing the decryption, if it fails - add a space and try again. But, I'd like to solve this the right way.
Any help would be appreciated!
-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA
-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA
All are valid points that I thought of too. I first thought I wasn't adding slashes or stripping slashes incorrectly. But, that wasn't the case. Then, I thought, maybe it should be a longer field length, so I doubled it from 32 to 64. This didn't make a difference. The size of the string never exceeds 32 chars.
I did a test - inserted a string with a character into the database. When pulling it from the database directly, the same string doesn't have the space at the end.
I just found this at http://www.peachpit.com while searching more for the solution:
"There is also a third, minor difference between these two: MySQL trims off extra spaces from CHAR columns when data is retrieved and from VARCHAR when it's inserted."
So, it looks like it is a MySQL thing. This article was talking about the differences in field types - no solution or info on how to disable this.
Any more suggestions?
I did a test - inserted a string with a character into the database. When pulling it from the database directly, the same string doesn't have the space at the end.
I just found this at http://www.peachpit.com while searching more for the solution:
"There is also a third, minor difference between these two: MySQL trims off extra spaces from CHAR columns when data is retrieved and from VARCHAR when it's inserted."
So, it looks like it is a MySQL thing. This article was talking about the differences in field types - no solution or info on how to disable this.
Any more suggestions?
OK, found the answer. Taken from mysql.com - surprise, surprise! Looks, like I'll have to use a BLOB or TEXT field. Hopefully, it doesn't use too much more space.
The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved.
The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. (Before MySQL 3.23, the length of CHAR may be from 1 to 255.) When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.
Values in VARCHAR columns are variable-length strings. You can declare a VARCHAR column to be any length from 0 to 255, just as for CHAR columns. (Before MySQL 4.0.2, the length of VARCHAR may be from 1 to 255.) However, in contrast to CHAR, VARCHAR values are stored using only as many characters as are needed, plus one byte to record the length. Values are not padded; instead, trailing spaces are removed when values are stored. This space removal differs from the standard SQL specification.
No lettercase conversion takes place during storage or retrieval.
If you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit.
If you need a column for which trailing spaces are not removed, consider using a BLOB or TEXT type. If you want to store binary values such as results from an encryption or compression function that might contain arbitrary byte values, use a BLOB column rather than a CHAR or VARCHAR column to avoid potential problems with trailing space removal that would change data values.
The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved.
The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. (Before MySQL 3.23, the length of CHAR may be from 1 to 255.) When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.
Values in VARCHAR columns are variable-length strings. You can declare a VARCHAR column to be any length from 0 to 255, just as for CHAR columns. (Before MySQL 4.0.2, the length of VARCHAR may be from 1 to 255.) However, in contrast to CHAR, VARCHAR values are stored using only as many characters as are needed, plus one byte to record the length. Values are not padded; instead, trailing spaces are removed when values are stored. This space removal differs from the standard SQL specification.
No lettercase conversion takes place during storage or retrieval.
If you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit.
If you need a column for which trailing spaces are not removed, consider using a BLOB or TEXT type. If you want to store binary values such as results from an encryption or compression function that might contain arbitrary byte values, use a BLOB column rather than a CHAR or VARCHAR column to avoid potential problems with trailing space removal that would change data values.
-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA