Microsoft SQL System Extendend Stored Procedure Overwrite sp_executesql

If what you are trying to do could be done, it would blow security wide open. Anyone could change SQL statements on the fly and do horrendous damage or fraud. Therefore it's unlikely, though knowing Microsoft, not necessarily impossible.

Some of the system stored procedures do more than a standard stored procedure, and use DLLs to implement themselves. They're not just a bunch of T-SQL statements lieke we would write.

I think you'll have to look for another method.

I concur with crescendo. SQL Server is designed to be extensible, but not mutable -- in other words, they've intentionally tried to design it so that you can't change system components, including system extended stored procedures. Unless someone from the SQL Server development team weighs in on this and says otherwise, I don't think you'll succeed with this. :(

Just as a friendly reminder:  You will find experts more responsive if you take the time to maintain your open questions.  You have asked 7 questions,  3 have been subsequently deleted, 1 force accepted and 3 are still open.  Here are all your open questions:

1 05/12/2004 500 Server Error 415 with HttpWebRequest  Open Internet Information Server
2 09/12/2003 250 IFRAME takes about 10-20 sec to load, ma...  Open Active Server Pages (ASP)
3 08/03/2004 500 Microsoft SQL System Extendend Stored Pr...  Open Microsoft SQL Server

As you can see one of the questions is nearly a year old.  It your choice, but also your responsibility.

Let us know.

I think you should do it and post your results.....

Do you have any idea how much you could screw up????  Not to mention if service packs come out and recreate the proc you replaced--then you have to move your proc back...

What's the end result you're looking for?  Maybe there are other ways...

could you share what is your goal. What would you like this procedure to do.

-- cheers

The goal here is that I want to extend some capability in Microsoft's Sharepoint Portal Server.  I have tried many ideas, and so far this is the best looking one, until I found out that I cannot replace the system extended stored procedure.  Basically I am trying to filter the results that are return to the application.  It sends a query string, I need to append some conditions to it, and then execute it.  When I remove the sp_executesql, it as expected fails because the stored proc isn't there.  when I try to register my custom version, it appears to register correctly, but the original functionality is also restored, bypassing my procedure.  When I try registering the dll with a non-system name, then it returns an error, so apparently my code is bad.  Do you think this could be the problem?  The error was a message box that said something like  bImpersonated is null... or cant use null reference.

Bottom line is the application calls sp_executesql.  I need to return a recordset based on the statement passed in after I modify it.

I understand the security issue, but I guess I figured that you shouldn't be able to add these things unless your an admin anyways.

Here is my code:  (I modified one of the microsoft samples)

/***********************************************************************
Copyright (c) 2000, Microsoft Corporation
All Rights Reserved.
***********************************************************************/
// This is an example of an extended procedure DLL built with Open Data
// Services. The functions within the DLL can be invoked by using the extended
//      stored procedures support in SQL Server.  To register the functions
// and allow all users to use them run the ISQL script XP_ODBC.SQL.
//
// For further information on Open Data Services refer to the Microsoft Open
// Data Services Programmer's Reference.
//
//  The extended procedures implemented in this DLL is:
//
//  XP_GETTABLE_ODBC -- Used to show the creation of a new connection to
//  SQL Server using ODBC that is bound to the initial client connection
#include <windows.h>
#include <tchar.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>
#include <srv.h>

// Miscellaneous defines.
#define XP_NOERROR              0
#define XP_ERROR                1

// Extended procedure error codes.
#define SRV_MAXERROR            50000
#define GETTABLE_ERROR          SRV_MAXERROR + 1

#define REMOTE_FAIL             4002

void handle_odbc_err(PSTR szODBCApi,
      SQLRETURN sret,
      DBINT msgnum,
      SQLHANDLE herror,
      SQLSMALLINT htype,
      SRV_PROC* srvproc);

// It is highly recommended that all Microsoft® SQL Server (7.0
// and greater) extended stored procedure DLLs implement and export
// __GetXpVersion. For more information see SQL Server
// Books Online
ULONG __GetXpVersion()

{
    return ODS_VERSION;
}


// XP_GETTABLE_ODBC
//    Returns the result of the SQL statement
//              select * from <szTable>
//
// Parameters:
//    srvproc - the handle to the client connection that
//    got the SRV_CONNECT.
//
// Returns:
//    XP_NOERROR
//    XP_ERROR
//
// Side Effects:
//    Returns messages and/or a result set to client.
RETCODE sp_executesql2(srvproc)
SRV_PROC *srvproc;
{

    HENV        henv        = SQL_NULL_HENV;
    HDBC        hdbc        = SQL_NULL_HDBC;
    HSTMT       hstmt       = SQL_NULL_HSTMT;
    SQLRETURN   sret;
    RETCODE     rc;

    char        acBindToken[256];

    // ODBC column attributes.
    TCHAR       acColumnName[MAXNAME];
    SQLINTEGER  cbColData;
    SQLSMALLINT eSQLType;
    SQLINTEGER  iNumAttr;
    SQLSMALLINT cbAttr;                // pointer to storage for descriptor info
    PBYTE*      ppData      = NULL;
    SQLINTEGER* pIndicators = NULL;
    DBINT       rows        = 0L;           // number of rows sent
    PTSTR       szDSN       = _T("local");  // for integrated security to work you need to
                                            // specify a local server in the ODBC setting
                                            // in the Control Panel in Windows
    int         bImpersonated;

    TCHAR       acUID[MAXNAME];
    TCHAR       acPWD[MAXNAME];

    int         nParams;
    DBINT       paramtype;

    TCHAR       szTable[MAXNAME * 3];   // database.owner.table
      TCHAR            szStatement[8012];
      TCHAR            szParams[8012];
      TCHAR            szValues[8012];
      TCHAR manip[5000];
      TCHAR temp[5000];

      _TCHAR *pdest;
      int result;
      
      TCHAR       szExec[128 + (MAXNAME * 3)];
    SQLSMALLINT nCols;
    SQLSMALLINT nCol;

    RETCODE     rcXP = XP_ERROR;        // Assume failure until shown otherwise.

      int loop;

    // Get number of parameters.
    nParams = srv_rpcparams(srvproc);  

    // Check number of parameters
    if (nParams < 1) {
          // Send error message and return
        srv_sendmsg(srvproc, SRV_MSG_ERROR, GETTABLE_ERROR, SRV_INFO, (DBTINYINT)0,
            NULL, 0, 0, "Error executing extended stored procedure: Invalid Parameter",
            SRV_NULLTERM);

        // A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
        // result set of an Extended Stored Procedure.
        srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
        return(XP_ERROR);
        }

    // All parameters should be varchars  (Statment, params, values) If not, send an
    // error and return.
      for(loop=1; loop <= nParams; loop++)
      {
            paramtype = srv_paramtype(srvproc, loop);
            if (paramtype != SRVVARCHAR) {
                  srv_sendmsg(srvproc, SRV_MSG_ERROR, GETTABLE_ERROR, SRV_INFO, (DBTINYINT)0,
                        NULL, 0, 0,
                        "Error executing extended stored procedure: Invalid Parameter Type",
                        SRV_NULLTERM);

                  // A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
                  // result set of an Extended Stored Procedure.
                  srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
                  return(XP_ERROR);
                  }
      }

      //do the special sharepoint processing for Docs
      //
      //  TODO
      //
      //
      
    // Allocate an ODBC environment handle
    sret = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
    if (sret != SQL_SUCCESS) {
        handle_odbc_err("SQLAllocHandle:Env",
            sret,
            (DBINT) REMOTE_FAIL,
            henv,
            SQL_HANDLE_ENV,
            srvproc);
        return(XP_ERROR);
        }
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3,
        SQL_IS_INTEGER);

    // Allocate an ODBC connection handle
    sret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    if (sret != SQL_SUCCESS) {
        handle_odbc_err("SQLAllocHandle:Dbc",
            sret,
            (DBINT)REMOTE_FAIL,
            henv,
            SQL_HANDLE_ENV,
            srvproc);

        SQLFreeHandle(SQL_HANDLE_ENV, henv);
        return(XP_ERROR);
        }

    // Check for integrated security.
    if (strcmp(srv_pfield(srvproc, SRV_LSECURE, (int *)NULL), "TRUE") == 0) {
        // Client has accessed using some form of integrated security
        // Impersonate client and set SQL_INTEGRATED_SECURITY option
        bImpersonated = srv_impersonate_client(srvproc);

        // Connect to DSN using integrated security
        SQLSetConnectAttr(hdbc, SQL_INTEGRATED_SECURITY,
            (SQLPOINTER) SQL_IS_ON, SQL_IS_INTEGER);
        _tcscpy(acUID, _T(""));
        _tcscpy(acPWD, _T(""));
        }
    else {
        // Client used standard login. Set the user name and password.
#ifdef UNICODE
        MultiByteToWideChar(CP_ACP, 0, srv_pfield(srvproc, SRV_USER, NULL),
            -1, acUID, MAXNAME);
        MultiByteToWideChar(CP_ACP, 0, srv_pfield(srvproc, SRV_PWD, NULL),
            -1, acPWD, MAXNAME);
#else
        strncpy(acUID, srv_pfield(srvproc, SRV_USER, NULL),
            MAXNAME);
        strncpy(acPWD, srv_pfield(srvproc, SRV_PWD, NULL),
            MAXNAME);
#endif
          }

    if (!SQL_SUCCEEDED(
        sret = SQLConnect(hdbc, (SQLTCHAR*) szDSN, SQL_NTS,
        (SQLTCHAR*) acUID, SQL_NTS, (SQLTCHAR*) acPWD, SQL_NTS)
        )) {
        handle_odbc_err("SQLConnect",
            sret,
            (DBINT)REMOTE_FAIL,
            hdbc,
            SQL_HANDLE_DBC,
            srvproc);
        goto SAFE_EXIT;
        }

    // Process data after successful connection
    sret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    if (sret != SQL_SUCCESS) {
        handle_odbc_err("SQLAllocHandle",
            sret,
            (DBINT)REMOTE_FAIL,
            hdbc,
            SQL_HANDLE_DBC,
            srvproc);
        return(XP_ERROR);
        }

    // Get the client session token...
    rc = srv_getbindtoken(srvproc, acBindToken);
    if (rc == FAIL) {
        srv_sendmsg(srvproc,
            SRV_MSG_ERROR,
            GETTABLE_ERROR,
            SRV_INFO,
            (DBTINYINT) 0,
            NULL,
            0,
            0,
            "Error with srv_getbindtoken",
            SRV_NULLTERM);
        srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
        return(XP_ERROR);
        }

    // ...bind it as an ODBC parameter for the stored procedure call...
    _tcscpy(szExec, _T("{call sp_bindsession(?)}"));
    sret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
        SQL_VARCHAR, 255, 0, acBindToken, 256, NULL);
    if (sret != SQL_SUCCESS) {
        handle_odbc_err("SQLBindParameter",
            sret,
            (DBINT)REMOTE_FAIL,
            hstmt,
            SQL_HANDLE_STMT,
            srvproc);
        return(XP_ERROR);
        }

    // ...and, using sp_bindsession, bind our session to the client's
    // session so that we share transaction space.
    sret = SQLExecDirect(hstmt, (SQLTCHAR*) szExec, SQL_NTS);
    if (!((sret == SQL_SUCCESS) ||(sret == SQL_SUCCESS_WITH_INFO))) {
        handle_odbc_err("SQLExecDirect",
            sret,
            (DBINT) GETTABLE_ERROR,
            hstmt,
            SQL_HANDLE_STMT,
            srvproc);
        return(XP_ERROR);
        }
    SQLFreeStmt(hstmt, SQL_RESET_PARAMS);

      // Execute the statement
    _tcscpy(szExec, _T("EXEC sp_executesql2 '"));
      memcpy( szStatement, srv_paramdata(srvproc, 1),      srv_paramlen(srvproc, 1));
      pdest = _tcsstr( szStatement, _T("WHERE") );
    result = (int)(pdest - szStatement + 1);
    memcpy(manip, szStatement, result);
      _tcscpy(temp, _T("LEFT OUTER JOIN ItemLevelPermissions ON (ItemLevelPermissions.UserGUID = t2.tp_GUID AND ItemLevelPermissions.DocId = t1.Id)"));
      _tcscat(manip,temp,_tcslen(temp));
    _tcscat(manip,pdest,_tcslen(szStatement)-result);
      
      
      _tcscat(szExec, manip);
    _tcscat(szExec, _T("'"));
      if(nParams > 1)
      {
            for(loop = 2; loop <= nParams; loop++)
            {
                  _tcscat(szExec, _T(",'"));
                  memcpy( szStatement, srv_paramdata(srvproc, loop), srv_paramlen(srvproc, 1));
                  _tcscat(szExec, szStatement);
                  _tcscat(szExec, _T("'"));
            }
      }

    _tcscpy(szExec, _T("SELECT TOP 10 * FROM Docs"));

   sret = SQLExecDirect(hstmt, (SQLTCHAR*) szExec, SQL_NTS);
    if (sret != SQL_SUCCESS) {
        handle_odbc_err("SQLExecDirect",
            sret,
            (DBINT) GETTABLE_ERROR,
            hstmt,
            SQL_HANDLE_STMT,
            srvproc);
        return(XP_ERROR);
        }

    // Get the number of columns in the ODBC result set.
    SQLNumResultCols(hstmt, &nCols);

    ppData = (PBYTE*) malloc(nCols * sizeof(PBYTE));
    pIndicators = malloc(nCols * sizeof(SQLINTEGER));

    if (ppData == NULL || pIndicators == NULL)
        goto SAFE_EXIT;

    // Build the column description for this results set.
    for (nCol = 0; nCol < nCols; nCol++) {
        // Get the column name, length and data type.
            SQLColAttribute(hstmt,
                  (SQLSMALLINT) (nCol + 1),
                  SQL_DESC_NAME,
                  (SQLTCHAR*) acColumnName,   // returned column name
                  MAXNAME,            // max length of rgbDesc buffer
                  &cbAttr,            // number of bytes returned in rgbDesc
                  &iNumAttr);

            SQLColAttribute(hstmt,
                  (SQLSMALLINT) (nCol + 1),
                  SQL_DESC_OCTET_LENGTH,
                  NULL,
                  0,              
                  NULL,          
                  &cbColData);    
      
        // Get the column's SQL Server data type, then reset the length
        // of the data retrieved as required.
        SQLColAttribute(hstmt,
                  (SQLSMALLINT) (nCol + 1),
                  SQL_CA_SS_COLUMN_SSTYPE,
                  NULL,
                  0,              
                  NULL,          
                  &eSQLType);

        // Over-write the column length returned by ODBC with the correct value
            //    to be used by ODS
            switch( eSQLType )  {
                  case SQLMONEYN:
                  case SQLMONEY:
                cbColData = sizeof(DBMONEY);
                break;

                  case SQLDATETIMN:
                  case SQLDATETIME:
                        cbColData = sizeof(DBDATETIME);
                        break;

                  case SQLNUMERIC:
                  case SQLDECIMAL:
                        cbColData = sizeof(DBNUMERIC);
                        break;


                  case SQLMONEY4:
                cbColData = sizeof(DBMONEY4);
                break;

                  case SQLDATETIM4:                       //smalldatetime
                        cbColData = sizeof(DBDATETIM4);
                        break;
                }


            // Allocate memory for row data.
        if ((ppData[nCol] = (PBYTE) malloc(cbColData)) == NULL)
            goto SAFE_EXIT;

        memset(ppData[nCol], 0, cbColData);

            // Bind column
            SQLBindCol(hstmt,
                  (SQLSMALLINT) (nCol + 1),
                  SQL_C_BINARY,           // No data conversion.
                  ppData[nCol],          
                  cbColData,              
                  &(pIndicators[nCol]));
      
            
            // Prepare structure that will be sent via ODS back to
            // the caller of the extended procedure
            srv_describe(srvproc,
                  nCol + 1,
                  acColumnName,
                  SRV_NULLTERM,
                  eSQLType,               // Dest data type.
                  (DBINT) cbColData,      // Dest data length.
                  eSQLType,               // Source data type.
                  (DBINT) cbColData,      // Source data length.
                  (PBYTE) NULL);
          }

    // Initialize the row counter
    rows = 0;

    // Get each row of data from ODBC until there are no more rows
    while((sret = SQLFetch(hstmt)) != SQL_NO_DATA_FOUND) {
        if (!SQL_SUCCEEDED(sret)) {
            handle_odbc_err("SQLFetch",
                sret,
                (DBINT) GETTABLE_ERROR,
                hstmt,
                SQL_HANDLE_STMT,
                srvproc);
            goto SAFE_EXIT;
            }

        // For each data field in the current row, fill the structure
        // that will be sent back to the caller.
        for (nCol = 0; nCol < nCols; nCol++) {
            cbColData = (pIndicators[nCol] == SQL_NULL_DATA ?
                0 : pIndicators[nCol]);
            srv_setcollen(srvproc, nCol+1, (int) cbColData);
            srv_setcoldata(srvproc, nCol+1, ppData[nCol]);
            }

        // Send the data row back to SQL Server via ODS.
        if (srv_sendrow(srvproc) == SUCCEED)
            rows++;
        }

    if (rows > 0)
            srv_senddone(srvproc, SRV_DONE_MORE | SRV_DONE_COUNT, (DBUSMALLINT)0, rows);
      else
            srv_senddone(srvproc, SRV_DONE_MORE, (DBUSMALLINT)0, (DBINT)0);


    // We got here successfully, let the client know.
    rcXP = XP_NOERROR;

SAFE_EXIT:
      // Free the data buffers.
    if (ppData != NULL)
        {
          for (nCol = 0; nCol < nCols; nCol++)
                free(ppData[nCol]);

        free(ppData);
        }
    if (pIndicators != NULL)
        free(pIndicators);
      
      // Free handles.
    if (hstmt != SQL_NULL_HSTMT)
          SQLFreeStmt(hstmt, SQL_DROP);

    if (hdbc != SQL_NULL_HDBC)
        {
          SQLDisconnect(hdbc);
          SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
        }

    if (henv != SQL_NULL_HENV)
          SQLFreeEnv(henv);

      // Revert back to SQL Server's user account.
    if( bImpersonated )
          srv_revert_to_self(srvproc);

      
    return (rcXP);
}


// HANDLE_ODBC_ERR
//  This routine is called to send messages to clients when an ODBC
//  function returns what could be considered an error (e.g., SQL_ERROR,
//  SQL_INVALID_HANDLE).
//
// Parameters:
//  szODBCApi   - The name of the failing function.
//  srODBAPI    - The SQLRETURN of the failing function.
//  msgnum      - The ODS user message code.
//  herror      - The ODBC handle involved in the error.
//  htype       - The ODBC handle type.
//  srvproc     - Contains additional client information.
//
// Returns:
//      none
//
void handle_odbc_err(PSTR szODBCApi,
      SQLRETURN sret,
      DBINT msgnum,
      SQLHANDLE herror,
      SQLSMALLINT htype,
      SRV_PROC* srvproc)
{
    SQLTCHAR    szErrorMsg[SQL_MAX_MESSAGE_LENGTH + 1];
    SQLSMALLINT cbErrorMsg;
    SQLSMALLINT nRec = 1;

    // If sret is SQL_SUCCESS, return without doing anything
    if (sret == SQL_SUCCESS)
        return;

    while (
        SQLGetDiagField(htype, herror, nRec++, SQL_DIAG_MESSAGE_TEXT,
        szErrorMsg, SQL_MAX_MESSAGE_LENGTH, &cbErrorMsg)
        == SQL_SUCCESS)
        {
        // If sret is SUCCESS_WITH_INFO, send as "message" (severity
        // <= 10, we use zero), else send to client as "error"
        // (severity > 10, we use 11).
        srv_sendmsg(srvproc,
            SRV_MSG_INFO,
            msgnum,
            (DBTINYINT) (sret == SQL_SUCCESS_WITH_INFO ? 0 : 11),
            (DBTINYINT) 1,
            NULL,
            0,
            0,
            szErrorMsg,
            SRV_NULLTERM);
        }
}

Do you need any help maintaining your old open questions?

<<I have tried many ideas, and so far this is the best looking one>>

Don't mean to be rude, but the others must have looked bad...

Are you sure that there's no way you can configure Sharepoint to do what you want? Messing with an application's SQL in a covert way seems destined to failure, doom and destruction. Tell us more about the Sharepoint stuff.

crescendo-  your right.  The other ways cannot be successful.  At every turn, this microsoft product has many unconfigurable areas.  Ultimately, I am trying to meet a very important requirement where we need item level permissions on documents in sharepoint, but sharepoint only allows per document library permissions.  A solution is to have a separate document library for each file.  At this point it seems like the only possible way to accomplish this, but the problem lies in a clean and cohesive UI.  I am attacking the problem from the back-end.  As far as the EULA, Im not worried, internal use only, and I have spoken with some Microsoft reps that said it shouldn't be an issue.  I think that if you buy the license, you can do whatever you want with it, just can't distribute. -separate discussion though.
The only other way I can see to get around this is by modifying the dll itself, and change the code which generates this sql string.  Issue there is its assembly language, MSIL, and I dont have 4 months to become a novice hack at it, and further doesn't necessarily guarantee that I would be hacking the right dll in the first place.  The applications organization is spread out between managed code, unmanaged code, SQL, XML, script language... very convoluted, very disappointing.

So - Item level permissions in sharepoint is really my issue, so that goes beyond the scope here, but my idea for a solution rests in SQL.  Any other comments are appreciated.

bowser17:

Have you tried the following site? It needs a login, but the contents page seems to address exactly what you are looking for:

http://msd2d.com/Tip_view.aspx?section=Exchange&category=Workflow

Yes, I've searched high and low.  Alot of content that is on that site pertains to Sharepoint 2001.  If I haven't mentioned it earlier, we are using Sharepoint 2003.  Other idea is:  can you create a network filter that sits in front of SQL?  Basically try to catch network traffic to SQL and modify it?  Yes, I know, security....

Worded very nicely crescendo :)

We are all aware of this.  I knew that the answer probably wasn't what I was hoping for, but I had to try!