Write a secure SQL Server extended stored procedure

SQL Server's extended storage process is actually a common Windows DLL, but some functions are implemented according to certain rules.
Recently, when writing an extended storage process, I found that there are still some important points to note when writing this kind of dynamic library. The reason for special attention is that DLL runs in the address space of SQL Server, and SQL server does not know how to schedule threads, even if we know it, it cannot be controlled.
We write a dynamic database for our own use. Even if it is used by others, it is rarely like SQL Server. A dynamic library is likely to be loaded multiple times, and are all loaded into the address space of a process. We know that when a dynamic library is loaded to the address space of the process, all global and local variables of the DLL are initialized and initialized only once. When the loadlibrary function is called again, it only adds the reference count. Obviously, if there is a global int, the initialization is 0, and a function is called to add itself, the value is 1, and then loadlibray is called, and use the returned handle to call the output function to output the value. Although the caller feels that the value is output immediately after loading, the value is indeed 1 rather than 0. Windows is a process independent, and in terms of threads, if you do not pay attention, the above situation is likely Program Personnel bring trouble.
This article introduces my extended storage process. The dynamic library exports three functions: init, work, final, and init to read files and store information in the memory, work simply retrieves information from the memory and recycles the memory in final. As mentioned above, if the problem of loading the same process space multiple times is not considered, calling init twice will cause unnecessary waste because I have read the memory for the first time. If I allocate the memory through the heap, it also causes memory leakage.
I used the reference count to solve this problem, Code Very short. paste it directly:
# 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, "part. dll cannot be loaded ");
Return xp_error;
}
Func thefunc = (func): getprocaddress (hinst, "init ");
If (! Thefunc ()){
: Freelibrary (hinst );
Printerror (psrvproc, "the table corresponding to the album category cannot be obtained ");
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 returned 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 think although it seems not very clever, the problem should be solved.
Another note is why TLS is not used. To be honest, I have considered using TLS because the code is actually problematic. If one user calls xp_part_init and the other calls xp_part_init, note that our stored procedure is on the server side, and then the first user calls xp_part_finalize, then what will happen? It still makes no matter whether xp_part_process can be used normally. However, the first user calls xp_part_finalize twice, the second user will be affected, and the xp_part_process will return an error.
TLS seems to solve this problem. For example, if you add another tls_index variable and call tlssetvalue to save your private data, tlsgetvalue is used to retrieve private data. When xp_part_init is used, if the private data is 0, run the normal initialization process (xp_part_init). After the successful execution, the private data is stored as 1. If it is 1, the private data is directly returned. When xp_part_finalize is executed, if the private data is 1, run the normal xp_part_finalize command and set the private data to 0. If the private data is 0, the system returns the result directly.
It seems that the idea is still good, so that multiple users are isolated, and security seems to have improved a lot. However, this is not feasible. Because TLS does not store private data but local variables in the thread, we cannot guarantee that a user's operations are performed in the same thread for multiple times, which is controlled by the SQL server itself, in fact, the results of multiple executions in the query analyzer show that the SQL server seems to use a thread pool internally. In this case, we can leave this idea alone.