proc.cpp

关于SQL SERVER 的加密和解密包

// proc.cpp
// xp_aesdecrypt extended stored procedure
//
// Usage:  xp_aesdecrypt @param1, @param2 [,@param3 OUTPUT]
//
// @param1 is the text to decrypt, @param2 is the key.  If @param3 OUTPUT is specified, 
// the result is returned in @param3, otherwise it is returned as a SQL result set.
//
// Michael Coles, MCDBA
// 7/2005
//
// Ver. 0.9.0.0: Updated 1/19/2006 to handle memory fragmentation problems that occur
//               when run over very large datasets
//
#include "../common/stdafx/stdafx.h"
#include "../common/base64/base64.h"
#include "../common/params/params.h"
#include "../common/aes/aes.h"

#define XP_NOERROR              0
#define XP_ERROR                1
#define MAXCOLNAME				25
#define MAXNAME					25
#define MAXTEXT					255

#define ERR_USAGE			"Usage:  EXEC xp_aesdecrypt @param1, @param2 [, param3 OUTPUT]"
#define ERR_KEY_SIZE		"Key (@param2) must be 128, 192 or 256 bits in length (16, 24 or 32 bytes)"
#define ERR_OUTPUT			"If @param3 is specified, it must be OUTPUT parameter"

#ifdef __cplusplus
extern "C" {
#endif

RETCODE __declspec(dllexport) xp_aesdecrypt(SRV_PROC *srvproc);

#ifdef __cplusplus
}
#endif

void PrintError (SRV_PROC *srvproc, char *ErrorMsg);

RETCODE __declspec(dllexport) xp_aesdecrypt(SRV_PROC *srvproc)
{
	SRVRETCODE sqlerr = XP_NOERROR;

	int j = params::getparamcount(srvproc);

	params *P1 = new params;
	params *P2 = new params;	
	params *P3 = new params;

	//BYTE *key = NULL;
	ULONG key_length = 0;
	BYTE *cipher_text = NULL;
	BYTE *plain_text = NULL;
	ULONG *rk = NULL;
	ULONG cipher_text_length = 0;
	ULONG plain_text_length = 0;

	// Get the parameters
	params::getparam(srvproc, 1, P1); // @param1 contains our plaintext
	params::getparam(srvproc, 2, P2); // @param2 is our key
	params::getparam(srvproc, 3, P3); // @param3 is our optional output parameter

	if (j < 2 || j > 3) {  // Less than 2 or more than 3 parameters
		PrintError (srvproc, ERR_USAGE);
		sqlerr = XP_ERROR;
	} else {

		// Get our key length
		key_length = P2->length;
		if (key_length != 16 && key_length != 24 && key_length != 32) {
			PrintError (srvproc, ERR_KEY_SIZE);
			sqlerr = XP_ERROR;
		} else {

			// Set up our Rijndael object
			Rijndael r;
			// Now define our rk
			rk = new ULONG[RKLENGTH(key_length * 8) + 10];
			// Get the number of rounds
			int nrounds = r.rijndaelSetupDecrypt(rk, P2->cdata, key_length * 8);
			// Now set up our cipher text and plain text buffers
			cipher_text_length = P1->length;
			cipher_text = new BYTE[cipher_text_length + 10];
			plain_text = new BYTE [cipher_text_length + 10];
			memset(cipher_text, 0, cipher_text_length + 10);
			memset(plain_text, 0, cipher_text_length + 10);
			memcpy(cipher_text, P1->cdata, cipher_text_length);
			// Temp pointers
			BYTE *PLAIN = plain_text;
			BYTE *ENC = cipher_text;
			for (ULONG i = 0; i < cipher_text_length; i += 16)
			{
				r.rijndaelDecrypt(rk, nrounds, ENC, PLAIN);
				ENC += 16;
				PLAIN += 16;
			}
			PLAIN = NULL;
			ENC = NULL;
			// Here we get the actual text length
			plain_text_length = cipher_text_length - *(plain_text + cipher_text_length - 1);
		
			if (j == 2) { // Only 2 params, so return a SQL result set
				DBCHAR colname [25];
				_snprintf(colname, 25, "DECRYPTED");
			    srv_describe(srvproc, 1, colname, SRV_NULLTERM, SRVVARCHAR, 32, SRVVARCHAR, 32, 0);
				srv_setcoldata(srvproc, 1, &plain_text);
				if (!P1->isnull)
					srv_setcollen(srvproc, 1, plain_text_length);
				else
					srv_setcollen(srvproc, 1, 0);
				srv_sendrow(srvproc);
				srv_senddone(srvproc, SRV_DONE_MORE | SRV_DONE_COUNT, (DBUSMALLINT)0, (DBINT)1);
			} else { // 3 params, so check if @param3 is OUTPUT type
				if (!P3->isoutput) {
					PrintError (srvproc, ERR_OUTPUT);
					sqlerr = XP_ERROR;
				} else { // Output result to @param3
					if (P1->isnull)
						srv_paramsetoutput(srvproc, 3, NULL, 0, TRUE);
					else
						srv_paramsetoutput(srvproc, 3, plain_text, plain_text_length, FALSE);
					srv_senddone(srvproc, SRV_DONE_MORE, (DBUSMALLINT)0, (DBINT)0);
				}
			}
		}
	}
	if (rk != NULL) {
		memset(rk, 0, RKLENGTH(key_length * 8) * sizeof(ULONG));
		delete [] rk;
		rk = NULL;
	}
	if (plain_text != NULL) {
		memset(plain_text, 0, plain_text_length);
		delete [] plain_text;
		plain_text = NULL;
	}
	if (cipher_text != NULL) {
		memset (cipher_text, 0, cipher_text_length);
		delete [] cipher_text;
		cipher_text = NULL;
	}
	if (P1 != NULL) {
		delete P1;
		P1 = NULL;
	}
	if (P2 != NULL) {
		delete P2;
		P2 = NULL;
	}
	if (P3 != NULL) {
		delete P3;
		P3 = NULL;
	}
	return sqlerr;
}
// This routine sends an error message to the SQL Server Output Window
//
// Parameters:
//		SRV_PROC *srvproc		The server process pointer passed in by SQL Server
//		char *ErrorMsg			The error message string to print
//
void PrintError (SRV_PROC *srvproc, char *ErrorMsg)
{
	// Print the error message on the SQL Server output screen
	srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_ERROR, SRV_INFO, 1,
		NULL, 0, (DBUSMALLINT) __LINE__, 
		ErrorMsg,
		SRV_NULLTERM);
	// Alert SQL Server that an error has occurred
	srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
}