Encryption with UTF8 charset issue

2 Message(s) by 2 Author(s) originally posted in mysql discussion

From: jackal_on_work

Date: Friday, October 26, 2007

HI faculties,
I have a table

<database> A collection of {records} in a {relational database}.

	(1997-06-04)

tbl_users that has charset set to UTF8. The
password

<security> An arbitrary string of characters chosen by a user
	or {system administrator} and used to authenticate the user
	when he attempts to log on, in order to prevent unauthorised
	access to his account.

	A favourite activity among unimaginative {computer nerds} and
	{crackers} is writing programs which attempt to discover
	passwords by using lists of commonly chosen passwords such as
	people's names (spelled forward or backward).  It is
	recommended that to defeat such methods passwords use a
	mixture of upper and lower case letters or digits and avoid
	proper names and real words.  If you have trouble remembering
	random strings of characters, make up an acronym like
	"ihGr8trmP" ("I have great trouble remembering my password").

	(1994-10-27)

column in this table, should store

[probably from "main store"] In some varieties of Commonwealth
	hackish, the preferred synonym for {core}.  Thus, "bringing a
	program into store" means not that one is returning
	shrink-wrapped software but that a program is being {swap}ped
	in.

	[{Jargon File}]

passwords in encrypted
form. For doing this, I am using the AES

1. <programming> {Application environment specification}.

	2. <security> {Advanced Encryption Standard}.

	(1999-11-03)

_ENCRYPT() function. To my
surprise, when I tried the following statement, no data

<data, data processing, jargon> /day't*/ (Or "raw data")
	Numbers, {characters}, {images}, or other method of recording,
	in a form which can be assessed by a human or (especially)
	input into a {computer}, stored and {processed} there, or
	transmitted on some {digital channel}.  Computers nearly
	always represent data in {binary}.

	Data on its own has no meaning, only when interpreted by some
	kind of {data processing system} does it take on meaning and
	become {information}.

	People or computers can find patterns in data to perceive
	information, and information can be used to enhance
	{knowledge}.  Since knowledge is prerequisite to wisdom, we
	always want more data and information.  But, as modern
	societies verge on {information overload}, we especially need
	better ways to find patterns.

	1234567.89 is data.

	"Your bank balance has jumped 8087% to $1234567.89" is
	information.

	"Nobody owes me that much money" is knowledge.

	"I'd better talk to the bank before I spend it, because of
	what has happened to other people" is wisdom.

	(1999-04-30)

got inserted
for the password column.

These are the statements:

create table tbl_users
(
id bigint(10),
login varchar(10),
password char(10)
) default charset = utf8;

insert into tbl_users values(1, 'dan', aes_encrypt('dan', 'test'));

select * from tbl_users;

select aes_decrypt(password, 'test') from tbl_users;The last select statement gives me a NULL value.

I have tried seeing the character

<character> An {atom} in a {character repertoire}.

	Compare with {glyph}.

	(1998-10-18)

set settings using the following:

show variables like '%char%'

Variable_name
Value
------------------------
-------------------------------------------------------
character_set_client
utf8
character_set_connection
utf8
character_set_database
utf8
character_set_filesystem
binary
character_set_results
utf8
character_set_server

1. A program which provides some service to other ({client})
	programs.  The connection between client and server is
	normally by means of {message passing}, often over a
	{network}, and uses some {protocol} to encode the client's
	requests and the server's responses.  The server may run
	continuously (as a {daemon}), waiting for requests to arrive
	or it may be invoked by some higher level daemon which
	controls a number of specific servers ({inetd} on {Unix}).

	There are many servers associated with the {Internet}, such as
	those for {HTTP}, {Network File System}, {Network Information
	Service} (NIS), {Domain Name System} (DNS), {FTP}, {news},
	{finger}, {Network Time Protocol}.  On Unix, a long list can
	be found in /etc/services or in the {NIS} database "services".
	See {client-server}.

	2. A computer which provides some service for other computers
	connected to it via a network.  The most common example is a
	{file server} which has a local disk and services requests
	from remote clients to read and write files on that disk,
	often using {Sun}'s {Network File System} (NFS) {protocol} or
	{Novell Netware} on {PCs}.  Another common example is a {web
	server}.

	[{Jargon File}]

	(2003-12-29)

latin1
character_set_system
utf8
character_sets_dir C:\Program Files\MySQL\MySQL Server 5.0\share
\charsets\What can be the possible problem? Please suggest.

Thanks in advance
Jackal

From: Rik Wasmus

Date: Friday, October 26, 2007

wrote in message:

HI faculties,
I have a table tbl_users that has charset set to UTF8. The
password column in this table, should store passwords in encrypted
form. For doing this, I am using the AES_ENCRYPT() function. To my
surprise, when I tried the following statement, no data got inserted
for the password column.
These are the statements:
create table tbl_users
(
id bigint(10),
login varchar(10),
password char(10)
) default charset = utf8;
insert into tbl_users values(1, 'dan', aes_encrypt('dan', 'test'));
select * from tbl_users;
select aes_decrypt(password, 'test') from tbl_users;
The last select statement gives me a NULL value.

RTFM:
Because AES is a block-level algorithm, padding is used to encode

1. <algorithm, hardware> To convert {data} or some physical
	quantity into a given format.  E.g. {uuencode}.

	See also {encoder}.

	2. <cryptography> To encrypt, to perform {encryption}.

	(1999-07-06)

uneven
length string

<programming> A sequence of {data} values, usually {bytes},
	which usually stand for {characters} (a "character string").
	The {mapping} between values and characters is determined by
	the {character set} which is itself specified implcitly or
	explicitly by the environment in which the string is being
	interpreted.

	The most common character set is {ASCII} but, since the late
	1990s, there has been increased interest in larger character
	sets such as {Unicode} where each character is represented by
	more than eight {bits}.

	Most programming languages consider strings (e.g.
	"124:shabooya:\n", "hello world") basically distinct from
	numbers which are typically stored in fixed-length {binary} or
	{floating-point} representation.

	A {bit string} is a sequence of {bits}.

	(1999-12-21)

s and so the result string length may be calculated using
this formula:
16 × (trunc(string_length / 16) + 1)

mysql> insert into tbl_users values(1, 'dan', aes_encrypt('dan', 'test'));
ERROR 1366 (HY000): Incorrect string value: '\xDF<\xA2\x90\xEB\x02...' for
column 'password' at row

<data, database, programming> An {ordered set} of {fields},
	usually stored contiguously.  The term is used with similar
	meaning in several different contexts.  In a file, a "record"
	probably has some fixed length, in contrast to a "line" which
	may have any length and is terminated by some {End Of Line}
	sequence).  A {database} record is also called a "row".  In a
	{spreadsheet} it is always called a "row".  Some programming
	languages use the term to mean a type composed of fields of
	several other types ({C} calls this a "{struct}").

	In all these cases, a record represents an entity with certain
	field values.

	Fields may be of a fixed width ({bits} or {characters}) or
	they may be separated by a {delimiter} character, often
	{comma} ({CSV}) or {HT} ({TSV}).

	In a database the list of values of a given field from all
	records is called a column.

	(2002-03-22)

1
mysql> select aes_encrypt('dan', 'test');
--
Rik Wasmus

Next Message: --defaults-extra-file

Blogs related to Encryption with UTF8 charset issue

[fm-news] Newsletter for Wednesday, October 10th 2007
About: PapyrusBB is a forum that was designed specifically for use with WikyBlog. It's written in PHP with a MySQL backend, fully integrates with the WikyBlog system using the AJAX-enhanced interface, and is UTF-8 compatible. ...

[fm-news] Newsletter for Wednesday, October 17th 2007
language/charset guesser for more 70 language/charset combinations is included, along with cluster support, making it possible to distribute data over multiple computers and index huge collections with up to several ...

vBulletin Installation und Umlaute
character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ ...

Ascii Characters
Creating a Table with Multiple Character Sets. Checking Character Set Setting. Storing ASCII Characters in Non-ASCII Columns. Storing Non-ASCII Characters . On Unix, where the default is normally UTF-8, this is a big problem, ...

MySQL Reference
(Older versions of MySQL limited identifiers to valid alphanumeric characters from the default character set, as well as $ and _.) This rule is limited, however, for databases and tables, because these values must be treated as files on ...

@rej I did geeklog that but i went back to change the makeconf so ...
looks fine here. yes. yet another reason for you to switch to UTF-8. are there known problems with emerging selectwm-0.4.1? we don’t do that tehdavid. however.. do you think it is in the portage tree then? this isn’t my issue. ...