about sql table record limitation

[QQfoolsorellina]

How can I make the register user_id and password limited for English letters and numbers.

I know PHP use RE can do it, but I wanna know if it possible add a check rule on mysql table?

[AnnieRuru]

if you mean character creation,

// Manage possible letters/symbol in the name of charater. Control character (0x00-0x1f) are never accepted. Possible values are:

// NOTE: Applies to character, party and guild names.

// 0: no restriction (default)

// 1: only letters/symbols in 'char_name_letters' option.

// 2: Letters/symbols in 'char_name_letters' option are forbidden. All others are possibles.

char_name_option: 1

// Set the letters/symbols that you want use with the 'char_name_option' option.

// Note: Don't add spaces unless you mean to add 'space' to the list.

char_name_letters: abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890

by looking in the source code

	// Check Authorised letters/symbols in the name of the character
	if( char_name_option == 1 )
	{ // only letters/symbols in char_name_letters are authorised
		for( i = 0; i < NAME_LENGTH && name[i]; i++ )
			if( strchr(char_name_letters, name[i]) == NULL )
				return -2;
	}
	else if( char_name_option == 2 )
	{ // letters/symbols in char_name_letters are forbidden
		for( i = 0; i < NAME_LENGTH && name[i]; i++ )
			if( strchr(char_name_letters, name[i]) != NULL )
				return -2;
	}

which it uses strchr to limit the characters

so it seems like SQL doesn't do the limit character operation, its in the source code do it

http://dev.mysql.com/doc/refman/5.5/en/string-types.html

I have read the documentation ... maybe it can't

if you want to limit it from npc script, keyworld released the script style string manipulation command

http://www.eathena.ws/board/index.php?showtopic=204303&hl=

which I think you can crack it

he used compare on all variables from the .@char$ array

if ( .@i == .@charsize ) -> then it is not a character in the array

Edited January 15, 2014 by AnnieRuru

[QQfoolsorellina]

My players are all Chinese and sometimes a few players enter Chinese  letter for registering new account (not mean character creation )

 

I just want to learn mysql how to limit character on specific filed without using register web with R E check,

 

Now I know there is no such operation can be used in mysql , thank you for supporting.

Edited January 15, 2014 by QQfoolsorellina

[nanakiwurtz]

Why do you want to limit the Chinese characters?

Use nvarchar instead of varchar, and you'll be fine. Since nvarchar can store any Unicode data, not like varchar which can only store 7-bit ASCII.

You can select whether to use UTF-8 or UTF-16.

UTF-8 is Western preferred with some Asian languages support, while UTF-16 is Asian preferred with some Western languages support.

Now you'd think about the storage space it would take if you switch to Unicode characters, and yes, the space it takes will be larger than the varchar.

By using Unicode, you're preparing for the future because your SQL data has better compatibility.

But I don't know if this can be used on rA or not 

Or why don't you check the strings for latin alphabet, number and some symbols you allow?

Other than that, shows a message that the player has to use normal character to register.

[AnnieRuru]

@nanakiwurtz

I am a chinese so I can understand what she is saying

 

How can I make the register user_id and password limited for English letters and numbers.

I know PHP use RE can do it, but I wanna know if it possible add a check rule on mysql table?

is it possible to create a sql table, the user_id and password column limited to A-Z alphabets and numeric numbers only ?

I know PHP can use Regular Expression to do something like this, but is it possible for MySQL to create a column field with this setting

I think her server is chinese based, but she just post here to double confirm MySQL can do this check or not

 

Use nvarchar instead of varchar, and you'll be fine. Since nvarchar can store any Unicode data, not like varchar which can only store 7-bit ASCII.

create table aaa (
id int(11) primary key,
value nvarchar(100)
);
select * from aaa;
drop table aaa;

tested, I still can input ~!$#&* to store the data

varchar also seems can store ~!$#&*

Edited January 15, 2014 by AnnieRuru

[nanakiwurtz]

SELECT * from aaa REGEXP '[[:alnum:]]+';

Edited January 16, 2014 by nanakiwurtz

[AnnieRuru]

prontera,164,185,5	script	kjkfjskf	100,{

input .@test$;

query_sql "select '"+ escape_sql( .@test$ )+"' regexp '^[a-z0-9]+$'", .@value;

dispbottom .@test$ +" -> "+( ( .@value )? "true" : "false" );

end;

}

[nanakiwurtz]

So this one is solved then?

[AnnieRuru]

no, there is no answer

 

her question is -> is it possible for the SQL to store a string which limited only to A-Z alphabets 0-9 numbers only

but I read through the documentations, it seems SQL doesn't have this feature ... <-- pls correct me if I'm wrong ...

http://dev.mysql.com/doc/refman/5.5/en/string-types.html

all the string types data can save ASCII symbols ....

 

the reason because we can actually do this kind of limitation, is coded from the source code (C language) or website (PHP)

what I did above is just checking true or false, I didn't save the value into the table

[nanakiwurtz]

Well she could use a secondary table, so it will be like:

Player -> web registration -> Secondary table -> regex to strip any characters else than ^[a-zA-Z0-9]+$ -> save to primary table...