MySQL column truncation (+Wordpress exploit)

[adis]

U augustu je Stefan Esser imao jedan zanimljiv post o novom tipu sigurnosnog propusta:
http://www.suspekt.org/2008/08/18/mysql-and-sql-column-truncation-vulnerabilities/

Dočekali smo i prvi masovni exploit za Wordpress, sad za sad samo password reset, ali nikad se ne zna:
http://wordpress.org/development/2008/09/wordpress-262

Uradite update svog wordpress bloga, isključite registraciju, promijenite bazu

Isključiva krivica leži na MySQL developerima, dopuštaju predugačak input (“admin x”), i umjesto da ga odbace, odrežu zadnji dio ("admin "), a to je prema SQL pravilima za char kolone ekvivalentno “admin”
(Inače, ko normalan koristi char kolone u 21. vijeku?)

Evo šta kaže PostgreSQL manual:

SQL defines two primary character types: character varying(n) and character(n), where n is a positive integer. Both of these types can store strings up to n characters in length. An attempt to store a longer string into a column of these types will result in an error, unless the excess characters are all spaces, in which case the string will be truncated to the maximum length. (This somewhat bizarre exception is required by the SQL standard.) If the string to be stored is shorter than the declared length, values of type character will be space-padded; values of type character varying will simply store the shorter string.

[vedran]

Stani, ako sam te dobro razumio, stringovi “admin” i "admin " su identični za mysql ali samo ako koristiš char, a ne varchar ?
Totalni idiotizam, ali kao što reče ko još danas koristi char.

[adis]

Ne samo za MySQL, to je po SQL standardu (razmaci na kraju stringa u char kolonama služe za padding, i ignoriraju se za većinu operacija). Problem je (kao i obično), što MySQL ladno dopušta space padded input sa dodatnim znakovima na kraju (tako da je prije inserta “admin x” različito od “admin”, a nakon inserta i truncationa, “admin” == "admin "). Prije verzije 5.0.3 MySQL je također i za varchar radio truncation. Fakat ne kontam zašto ljudi i dalje koriste takav POS od baze…

Dodatak:
http://dev.mysql.com/doc/refman/5.0/en/char.html

All MySQL collations are of type PADSPACE. This means that all CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces. For example:

http://www.postgresql.org/docs/8.3/interactive/datatype-character.html

Trailing spaces are disregarded when comparing two values of type character, and they will be removed when converting a character value to one of the other string types. Note that trailing spaces are semantically significant in character varying and text values.

P.S.
MySQL je pravo urnebesan. Ništa lično, samo mnogo tehničkih nedostataka