Learning More About Encryption

I would like to thank "Manipuflation" for kindly linking to my blog with his (check it out here: http://www.boatingaccidentnews.com/), and giving me some inspiration to do some more work into how to make communications more secure OR how to just spoof on-line spies...

Many of you readers know that I keep a sales database of our customers purchases of our bearings.  At rare times I want to publish a list of customers to show prospects there that we sell "real amounts" of bearings, but we do not want them to know who those customers are.  So, I wrote up some SQL to encrypt the customer identification number and the name.  Here is the SQL, but do note that I changed a couple of numbers buried deep in there...!

SELECT DISTINCTROW (((Right(([df_2].RUC),2) Mod 2) + 1) * 10)  & (Right(Left((([df_2].RUC) * 599),11),10)) AS Cust_ID, (Right(Left(([df_2].Cliente),4),1)) & (Left(Right(([df_2].Cliente),3),1)) & (Left(Right(([df_2].Cliente),2),1)) & (Right(Left(([df_2].Cliente),4),1)) AS Customer, Sum([df_2].Cant) AS Sum_of_Qty, Sum(CCur([Cant]*[VUnit]*((100-[Dscto])/100))) AS Expr9
FROM df_2
WHERE RIGHT(([df_2].Fecha),2) = "13"
GROUP BY [df_2].Cliente, [df_2].RUC;

There is a lot going on in the above SQL (multiplying by a prime number, selecting digits in derived numbers, and a modular function...) my table "df_2" is where I keep the raw sales numbers for each line item we sell.  "Expr9" is the sales amount for that line-item transaction (eg, 10 pcs of KBC's 6007-RS * price also taking into account any discounts the customer gets).

Here is some output, customers (in Peru) of ours who bought between $1725 and $3999 of bearings so far this year.  Cliente "DEMA S.A." doesn't care about this, I chose this customer kind-of at random.

Note a few things:

1)  Field "RUC" (4th column) is the Peruvian equivalent of our "Employer ID".  In Peru, companies have a RUC that begins with "20", individuals have one starting with "10".  The RUC in Peru is always a 11 digit number.  The more secure "Cust_ID" has 12 digits, note that they ALL start with a "10" or a "20", but they do not correlate with the actual RUCs (illustrated here in DEMA's case).

2)  Field "Customer" (2nd column) has letters from their company or individual names selected and manipulated.  Obviously this is not secure, as a determined opponent (does not even have to be highly skilled) could likely break this one.  But, this column does illustrate the power of SQL to do interesting things...

3)  Cust_ID "102244777431" (yellow background)  in reality has a different RUC than all of the others...  But, that's OK!

Cust_ID	Customer	Expr9	RUC	Cliente	Expr9
201100995727	T  E	$3,953.53
101122714803	A  S	$3,871.25
101104110287	S	$3,801.64
200223015427	I  N	$3,779.98
201184309840	D  E	$3,779.79
101137504553	T  E	$3,769.60
201038625952	O  I	$3,752.56
100270206593	N  T	$3,732.45
100280100937	C  Z	$3,572.07
100264297331	U  I	$3,500.00
201149974507	T  E	$3,408.61
100347361191	.	$3,306.45	20101177042	DEMA S.A.	$3,306.45
201058142711	M  I	$3,293.20
200899122422	C  O	$3,282.54
101216960314	B  R	$3,268.19
201012244233	E  E	$3,206.17
201236418685	E  I	$3,196.13
201196824722	T  M	$3,191.13
200662267280	I  N	$3,180.86
200280635379	O  E	$3,154.26
201151574598	M	$3,089.46
200886714958	C  A	$3,081.76
200371561753	T  E	$3,021.68
100571182924	U	$2,870.56
200080399711	N	$2,852.66
101180346160	I  A	$2,784.72
101223489741	T  M	$2,779.56
100923579847	V  A	$2,734.62
201158429422	E	$2,658.51
200247314991	E	$2,601.10
101225710643	R  N	$2,569.06
101198401177		$2,492.70
201180378587	W	$2,482.15
200503780825	N	$2,465.20
101171985039	O	$2,407.90
101172304647	A  O	$2,393.96
201013209708	C  O	$2,378.50
201122578126	S  E	$2,378.00
200666804390	H	$2,357.43
100899997300	N	$2,309.04
200409762317	R  P	$2,231.55
101000314669	P  R	$2,110.52
100124674696	O	$2,104.40
100919336488	A  E	$2,060.47
100519873122	A	$2,030.54
200727516387	A  R	$1,935.53
100222739935	D	$1,893.24
201120810742	N	$1,866.79
101184341487	T  E	$1,865.65
201151494679	A	$1,844.80
100967736733	O  R	$1,839.31
102244777431	A	$1,828.20
100533511691	E	$1,775.91
201179506448	P  M	$1,737.58
200514984358	A  O	$1,726.32

***

By only using columns 1 and 3 (and of course a bigger prime than 599 (in the SQL coding near the right side, highlighted in red), or multiplying two or more primes together), you can really make this a much tougher nut to crack.

Again, this is only an example!  It is "good enough" to shield this kind of information from almost anyone with out advanced decryption technology and good computers.