proc.cpp

关于SQL SERVER 的加密和解密包

// xp_generatekey
//
// Michael Coles, MCDBA
//
// This XP generates a random encryption key with the specified number of bits
//
// Ver. 0.9.0.0: Modified to fix potential memory fragmentation for very large
//               datasets
//
#include "../common/stdafx/stdafx.h"
#include "../common/base64/base64.h"
#include "../common/params/params.h"
#include <stdlib.h>
#include <time.h>

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

#define ERR_USAGE			"Usage:  EXEC xp_generatekey @param1 [, @param2 OUTPUT]"
#define ERR_KEY_SIZE		"Key length (@param1) must be 32 - 448 (bits), and a multiple of 8"
#define ERR_OUTPUT			"If @param2 is specified, it must be OUTPUT parameter"

#ifdef __cplusplus
extern "C" {
#endif

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

#ifdef __cplusplus
}
#endif

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

union paramunion {
	char *int1;
	short *int2;
	long *int4;
	BYTE *cdata;
};

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

	int j = params::getparamcount(srvproc);
	
	BYTE *encodedkey;
	ULONG encodesize;
	BYTE *key;
	ULONG keylength;

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

	paramunion P1values;

	if (j < 1 || j > 2) {  // Less than 1 or more than 2 parameters
		PrintError (srvproc, ERR_USAGE);
		sqlerr = XP_ERROR;
	} else {
		// Get the parameters
		params::getparam(srvproc, 1, P1); // @param1 contains our requested key size in bits
		params::getparam(srvproc, 2, P2); // @param2 is our optional output param

		P1values.cdata = P1->cdata;

		switch (P1->length)
		{
			case 1:
				keylength = *P1values.int1;
				break;
			case 2:
				keylength = *P1values.int2;
				break;
			case 4:
				keylength = *P1values.int4;
				break;
			default:
				PrintError (srvproc, ERR_USAGE);
				sqlerr = XP_ERROR;
				break;
		}
		if (sqlerr == XP_NOERROR && (keylength % 8 != 0 || keylength < 32 || keylength > 448)) {
			PrintError (srvproc, ERR_KEY_SIZE);
			sqlerr = XP_ERROR;
		}
		if (sqlerr == XP_NOERROR) {
			keylength = keylength / 8;
			key = new BYTE [keylength + 1];
			memset(key, 0, keylength + 1);
			// seed the timer
			srand((unsigned)time(NULL));
			for (ULONG i = 0; i < keylength; i++)
				*(key + i) = rand() % 256;
			encodesize = CBase64Utils::CalculateEncodeBufferSize(keylength);
			encodedkey = new BYTE [encodesize + 1];
			memset(encodedkey, 0, encodesize + 1);
			CBase64Utils::Encode(key, encodedkey, keylength);
	
			if (j == 1) { // Only 1 param, so return a SQL result set
				DBCHAR colname [25];
				_snprintf(colname, 25, "KEY");
			    srv_describe(srvproc, 1, colname, SRV_NULLTERM, SRVVARCHAR, encodesize, SRVVARCHAR, encodesize, 0);
				srv_setcoldata(srvproc, 1, encodedkey);
				srv_setcollen(srvproc, 1, encodesize);
				srv_sendrow(srvproc);
				srv_senddone(srvproc, SRV_DONE_MORE | SRV_DONE_COUNT, (DBUSMALLINT)0, (DBINT)1);
			} else { // 2 params, so check if @param2 is OUTPUT type
				if (!P2->isoutput) {
					PrintError (srvproc, ERR_OUTPUT);
					sqlerr = XP_ERROR;
				} else { // Output result to @param3
					srv_paramsetoutput(srvproc, 2, encodedkey, encodesize, P1->isnull);
					srv_senddone(srvproc, SRV_DONE_MORE, (DBUSMALLINT)0, (DBINT)0);
				}
			}
		}
		
	}
	P1values.cdata = NULL;
	P1values.int1 = NULL;
	P1values.int2 = NULL;
	P1values.int4 = NULL;
	// Clean-up
	if (key != NULL) {
		memset(key, 0, keylength);
		delete [] key;
		key = NULL;
	}
	if (encodedkey != NULL) {
		memset (encodedkey, 0, encodesize);
		delete [] encodedkey;
		encodedkey = 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);
}