Writing secure SQL Server extended stored procedures

server| security | Stored procedures SQL Server extended stored procedures, in fact, is a common Windows DLL, but in accordance with some of the rules to implement some functions.
When writing an extended stored procedure recently, there are some areas that need special attention when writing this kind of dynamic library. The special note is that because DLLs run in the address space of SQL Server, and how SQL Server does thread scheduling, it's not something we can understand, even if we don't understand it.
We write dynamic libraries for our own use, even for others, and rarely, like SQL Server, a dynamic library is likely to load multiple times and is loaded into the address space of a process. We know that when a dynamic library is loaded into the address space of the process, all global and local variables of the DLL are initialized and initialized only once, and when the LoadLibrary function is called again, only the reference count is increased, so it is clear that if there is a global int, initialized to 0, Call a function to its own, at which point the value is 1, then call Loadlibray, and use the returned handle to invoke the output function to output the value, although the caller feels that it will output immediately after loading, and then the value is indeed 1 instead of 0. Windows is process independent, and on the thread side, if not, the above situation is likely to cause trouble for programmers.
Describes my extended stored procedure, which exports three functions: Init,work,final,init read files, store information in memory, work simply retrieve information to that memory, and Final reclaim memory. As mentioned above, if you do not consider the same process space multiple loading problems, two call init will cause unnecessary waste, because the first time I have read the memory, if the heap allocated memory, will also cause memory leaks.
I use the reference count to solve the problem, the code is very short, directly posted:
#include "stdafx.h"
#include <string>
using namespace Std;
extern "C" {
RETCODE __declspec (dllexport) xp_part_init (Srv_proc *srvproc);
RETCODE __declspec (dllexport) xp_part_process (Srv_proc *srvproc);
RETCODE __declspec (dllexport) xp_part_finalize (Srv_proc *srvproc);
}
#define XP_NOERROR 0
#define XP_ERROR 1
HINSTANCE hinst = NULL;
int nref = 0;
void Printerror (Srv_proc *psrvproc, char* szerrormsg);
ULONG __GetXpVersion () {return ods_version;}
Srvretcode xp_part_init (srv_proc* psrvproc) {
typedef BOOL (*FUNC) ();
if (Nref = = 0) {
Hinst =:: LoadLibrary ("Part.dll");
if (hinst = = NULL) {
Printerror (Psrvproc, "Cannot load Part.dll");
return xp_error;
}
Func Thefunc = (Func):: GetProcAddress (Hinst, "Init");
if (!thefunc ()) {
:: FreeLibrary (Hinst);
Printerror (Psrvproc, "Can not get the category number and album of the corresponding Table");
return xp_error;
}
}
+ + Nref;
return (XP_NOERROR);
}
Srvretcode xp_part_process (srv_proc* psrvproc) {
typedef BOOL (*FUNC) (char*);
if (Nref = = 0) {
Printerror (Psrvproc, "The function has not been initialized, please call Xp_part_init first");
return xp_error;
}
Func Thefunc = (Func):: GetProcAddress (Hinst, "get");
BYTE btype;
ULONG Cbmaxlen,cbactuallen;
BOOL Fnull;
Char szinput[256] = {0};
if (Srv_paraminfo (Psrvproc, 1, &btype, (ulong*) &cbmaxlen, (ulong*) &cbactuallen, (byte*) SzInput, & fnull) = = FAIL) {
Printerror (Psrvproc, "srv_paraminfo return FAIL");
return xp_error;
}
Szinput[cbactuallen] = 0;
string strinput = Szinput;
string stroutput = ";";
int cur,old = 0;
while (String::npos!= (cur = strinput.find ('; ", old)) {
strncpy (Szinput,strinput.c_str () + old,cur-old);
Szinput[cur-old] = 0;
Old = cur + 1;
Thefunc (Szinput);
if (String::npos ==stroutput.find ((string); "+ Szinput))
Stroutput + = Szinput;
}
strcpy (Szinput,stroutput.c_str ());
if (FAIL = = Srv_paramsetoutput (Psrvproc, 1, (byte*) (Szinput + 1), strlen (szinput)-1,false)) {
Printerror (Psrvproc, "Srv_paramsetoutput call failed");
return xp_error;
}
Srv_senddone (Psrvproc, Srv_done_count | Srv_done_more), 0, 0);
return xp_noerror;
}
Srvretcode xp_part_finalize (srv_proc* psrvproc) {
typedef void (*FUNC) ();
if (nref = 0)
return xp_noerror;
Func Thefunc = (Func):: GetProcAddress (Hinst, "Fin");
if ((--nref) = = 0) {
Thefunc ();
:: FreeLibrary (Hinst);
Hinst = NULL;
}
return (XP_NOERROR);
}
I don't think it seems very clever, but the problem should be solved.
There is also a point to explain why not use TLS, honestly, I have considered the use of, because in fact the code is a bit of a problem, if a user calls Xp_part_init, and then another user also call Xp_part_init, note that our stored procedures are server-side, Then the first user calls Xp_part_finalize, then how, he still can use xp_part_process normally, this does not matter, however, the first user calls two times xp_part_finalize, can affect the second user, his xp_ Part_process will return an error.
Using TLS seems to solve this problem, such as adding a Tls_index variable, invoking TlsSetValue to save user private data, TlsGetValue retrieving private data, and when Xp_part_init, if the private data is 0, perform the normal initialization process, (That is, the above xp_part_init) after the successful implementation of the storage of private data for 1, if it is 1, direct return, Xp_part_finalize, if the private data is 1, then the normal xp_part_finalize, and then set private data for 0, if it is 0, return directly.
As if the idea is still good, so the isolation of multiple users, security seems to improve a lot, but the fact is not feasible. Because TLS holds not private data but thread-local variables, we cannot guarantee that a user's multiple operations are performed with the same thread, which is controlled by SQL Server itself, and in fact the results I have executed repeatedly in the Query Analyzer show that the SQL A thread pool appears to be in use within the server. That being the case, the idea can only be abandoned.