这两天在做一个会员系统,客户要求对会员的余额进行加密。
余额是浮点数,但考虑到10进制的浮点数以2进制的方式保存到数据库中总是有各种情况的精度丢失,所以和小组商量,干脆就用两个整数来表示这个浮点数,int_section,float_section呵呵,整数部分,小数部分,字面翻译^_^..
余额只有2个操作接口,一个是读取余额,参数是用户id,返回一行两列(int_section,float_section),另一个是修改余额,参数是用户id和整数部分小数部分。至于充值、消费等,都是通过这两个操作进行。为了更安全,余额在数据库中也不能明文表示,所以,修改余额再获取了要修改的用户号和金额后,将金额加密存入数据库,读取余额时,获取加密的数据再解密输出。
一番纠结之后,我想出了一种双向加密解密的算法,比如将用户号为’1’的用户的余额修改为12345.6789,只需这样执行sql语句:
select update_balance('1',12345,6789);
完毕后,数据库中将会以以下形式保存记录:
用户的余额已经分3个字段s1,s2,s3加密保存了。
当需要读取用户余额时,只需要执行:
call get_balance('1');
相应的余额即会被解密出来:
另外,如果有人改动了数据库中字段的内容,比如
那么查询到的余额将是-1
具体实现思路是:以小数点为分界,将数字的两部分以右边第一位为奇数,再分别分成技术部分和偶数部分
例如下面的12345.6789和123123.123
s1储存奇数部分,也就是13579所含的信息
s2储存偶数部分,也就是2479所含的信息
s3储存小数点的位置信息所以,在获取到12345.6789后,先将13579分离出来,接着,第一位以小写字母’o’代表零,’p’代表1,’q’代表2…即小写字母char(ascii(‘o’)+n)代表数字n。
这里是13579所以s1的第一位是‘p’
之后,第一位后面的数字,都可以表示为前一个数字加上一个值或者减去一个值
1 +2(3) +2(5) +2(7) +2(9) 就像这样
而加减数字的值我们又可以再以这样定义的规则去表示:
按照上述规则,s1就变成了pcccc
我们还需要用一位作为校验,我的规则是,将13579所有位相加,再对7求余数
1+3+5+7+9=25,对7求余数,结果是4,4再按照第一位的方法转换成ascii码比‘o’大4的字符就是’s’
于是得到了s1的最终结果pccccs
同样的,可以得到s2:qcdcu
s3记录小数点的位置信息,我们只需记录小数部分,有几位是奇数位,有几位是偶数位就可以了。
比如12345.6789,就是2个奇数位,2个偶数位
再比如123123.123,就是2个奇数位,1个偶数位
这两个值,依然使用前面以字符’o’为基准的转换方法,2->‘p’
22就表示为pp,最后,用同样的方法加上一位校验位,(2+2)%7最后得到’s’
话说前面的’yk’是客户的检测,加着好玩的= =、、
解密的话知道加密的规则就好说了,另外这些校验位,如果发现不一致,那么最后的结果就会被强制赋值为-1再输出。
最后附上mysql下的加密解密(函数和存储过程)主要代码:
更新(加密):
CREATE FUNCTION `update_balance`(
id char(20),
int_section int,
float_section int) RETURNS varchar(10)
begin
declare section_0 int;
declare i int;
declare s1_int int;
declare s1_int_ int;
declare s2_int int;
declare s2_int_ int;
declare _odd int;
declare _even int;
declare y_s1 int;
declare y_s2 int;
declare y_s3 int;
declare n_odd int;
declare n_even int;
declare temp1 int;
declare temp2 int;
declare s1 varchar(10);
declare s2 varchar(10);
declare s3 varchar(10);
set y_s1=0;
set y_s2=0;
set y_s3=0;
set n_odd=0;
set n_even=0;
set i=0;
set s1_int=0;
set section_0=int_section;
while section_0>0 do
set y_s1=y_s1+mod(section_0,10);
set s1_int=s1_int+mod(section_0,10)*pow(10,i);
set section_0=section_0 div 100;
set i=i+1;
end while;
set n_odd=n_odd+i;
set i=0;
set s1_int_=0;
set section_0=float_section;
while section_0>0 do
set y_s1=y_s1+mod(section_0,10);
set s1_int_=s1_int_+mod(section_0,10)*pow(10,i);
set section_0=section_0 div 100;
set i=i+1;
end while;
set n_odd=n_odd+i;
set _odd=i;
set s1_int=s1_int*pow(10,i)+s1_int_;
set i=0;
set s2_int=0;
set section_0=int_section;
set section_0=section_0 div 10;
while section_0>0 do
set y_s2=y_s2+mod(section_0,10);
set s2_int=s2_int+mod(section_0,10)*pow(10,i);
set section_0=section_0 div 100;
set i=i+1;
end while;
set n_even=n_even+i;
set i=0;
set s2_int_=0;
set section_0=float_section;
set section_0=section_0 div 10;
while section_0>0 do
set y_s2=y_s2+mod(section_0,10);
set s2_int_=s2_int_+mod(section_0,10)*pow(10,i);
set section_0=section_0 div 100;
set i=i+1;
end while;
set n_even=n_even+i;
set _even=i;
set s2_int=s2_int*pow(10,i)+s2_int_;
set n_odd=n_odd-1;
set temp1=s1_int div pow(10,n_odd);
set s1=char(temp1+ascii('o'));
set s1_int=s1_int-(s1_int div pow(10,n_odd))*pow(10,n_odd);
set n_odd=n_odd-1;
while n_odd>=0 do
set temp2=s1_int div pow(10,n_odd);
set s1=concat(s1,if(temp1=temp2,'o',if(temp1<temp2,char(temp2-temp1+ascii('a')),char(ascii('z')-temp1+temp2))));
set s1_int=s1_int-(s1_int div pow(10,n_odd))*pow(10,n_odd);
set n_odd=n_odd-1;
set temp1=temp2;
end while;
set n_even=n_even-1;
set temp1=s2_int div pow(10,n_even);
set s2=char(temp1+ascii('o'));
set s2_int=s2_int-(s2_int div pow(10,n_even))*pow(10,n_even);
set n_even=n_even-1;
while n_even>=0 do
set temp2=s2_int div pow(10,n_even);
set s2=concat(s2,if(temp1=temp2,'o',if(temp1<temp2,char(temp2-temp1+ascii('a')),char(ascii('z')-temp1+temp2))));
set s2_int=s2_int-(s2_int div pow(10,n_even))*pow(10,n_even);
set n_even=n_even-1;
set temp1=temp2;
end while;
set s3='yk';
set s3=concat(s3,char(_odd+ascii('o')),char(_even+ascii('o')));
set y_s3=_odd+_even;
set s1=concat(s1,char(mod(y_s1,7)+ascii('o')));
set s2=concat(s2,char(mod(y_s2,7)+ascii('o')));
set s3=concat(s3,char(mod(y_s3,7)+ascii('o')));
call modify_balance(id,s1,s2,s3);
return s3;
end;
读取(解密):
CREATE PROCEDURE `get_balance`(
in member_id char(20)
)
begin
declare m_s1 varchar(10);
declare m_s2 varchar(10);
declare m_s3 varchar(10);
declare n_s1 int;
declare n_s2 int;
declare odd int;
declare even int;
declare float_odd int;
declare float_even int;
declare int_section int;
declare float_section int;
declare temp1 int;
declare temp2 int;
declare temp int;
declare i int;
declare fuck bool;
set fuck=false;
select s1,s2,s3 into m_s1,m_s2,m_s3
from yk_balance
where member_id=yk_member_id;
set n_s1=length(m_s1);
set n_s2=length(m_s2);
set i=1;
set odd=ascii(substr(m_s1,i,1))-ascii('o');
set n_s1=n_s1-1;
set temp1=odd;
set i=i+1;
while n_s1>1 do
set temp2=if(ascii(substr(m_s1,i,1))=ascii('o'),0,if(ascii(substr(m_s1,i,1))<=ascii('k'),ascii(substr(m_s1,i,1))-ascii('a'),ascii(substr(m_s1,i,1))-ascii('z')));
set temp1=temp1+temp2;
set fuck=if(temp>9 or temp<0,true,fuck);
set n_s1=if(fuck,0,n_s1);
/*if(temp>9 or temp<0);*/
set odd=temp1+odd*10;
set n_s1=n_s1-1;
set i=i+1;
end while;
set temp1=odd;
set temp=0;
while temp1>0 do
set temp=temp+mod(temp1,10);
set temp1=temp1 div 10;
end while;
set fuck=if(mod(temp,7)=ascii(substr(m_s1,length(m_s1),1))-ascii('o'),fuck,true);
set i=1;
set even=ascii(substr(m_s2,i,1))-ascii('o');
set n_s2=n_s2-1;
set temp1=even;
set i=i+1;
while n_s2>1 do
set temp2=if(ascii(substr(m_s2,i,1))=ascii('o'),0,if(ascii(substr(m_s2,i,1))<=ascii('k'),ascii(substr(m_s2,i,1))-ascii('a'),ascii(substr(m_s2,i,1))-ascii('z')));
set temp1=temp1+temp2;
/*if(temp>9 or temp<0);*/
set even=temp1+even*10;
set n_s2=n_s2-1;
set i=i+1;
end while;
set temp1=even;
set temp=0;
while temp1>0 do
set temp=temp+mod(temp1,10);
set temp1=temp1 div 10;
end while;
set fuck=if(mod(temp,7)=ascii(substr(m_s2,length(m_s2),1))-ascii('o'),fuck,true);
set temp1=ascii(substr(m_s3,3,1))-ascii('o');
set temp2=ascii(substr(m_s3,4,1))-ascii('o');
set fuck=if((temp1+temp2)=ascii(substr(m_s3,5,1))-ascii('o'),fuck,true);
set float_section=0;
set i=1;
set temp1=if(fuck,0,temp1);
set temp2=if(fuck,0,temp2);
while temp1>0 or temp2>0 do
set float_section=float_section*10+mod(if(mod(i,2)=1,odd,even),10);
set temp1=if(mod(i,2)=1,temp1-1,temp1);
set temp2=if(mod(i,2)=1,temp2,temp2-1);
set odd=if(mod(i,2)=1,odd div 10,odd);
set even=if(mod(i,2)=1,even,even div 10);
set i=i+1;
end while;
set int_section=0;
set i=1;
set odd=if(fuck,0,odd);
set even=if(fuck,0,even);
while odd>0 or even>0 do
set int_section=int_section*10+mod(if(mod(i,2)=1,odd,even),10);
set odd=if(mod(i,2)=1,odd div 10,odd);
set even=if(mod(i,2)=1,even,even div 10);
set i=i+1;
end while;
set int_section=if(fuck,0,int_section);
set temp=0;
while int_section>0 do
set temp=temp*10+mod(int_section,10);
set int_section=int_section div 10;
end while;
set int_section=temp;
set float_section=if(fuck,0,float_section);
set temp=0;
while float_section>0 do
set temp=temp*10+mod(float_section,10);
set float_section=float_section div 10;
end while;
set float_section=temp;
select if(fuck,-1,int_section) as int_section,if(fuck,-1,float_section) as float_section;
end;
最后蛋疼一下:弄了这么多,敲了这么绕,这么蛋疼的代码,并没有什么卵用!
倒是以后想做点加密的时候可以参考下= =。。
//the end