Is Direct SQL Statement Secure ?

Alexnaldo C Santos — Thu, 22 Feb 2007 09:12:25 GMT

Hi,

I can run this code in ANY BO.

public void GetAllDatabaseTables()
{

string TABLE_SQL = @"SELECT TABLE_CATALOG AS [Database], TABLE_SCHEMA AS Owner, TABLE_NAME AS Name, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_NAME <> N'sysdiagrams') AND (TABLE_NAME <> N'dtproperties')";
this.FillDataTable(TABLE_SQL);
}

Any BO user that don't have access for table X, can access it easy...

I don't think direct SQL statement secure !

One malicious user can do :

1)Connection to my ES using the SDK. Remember! He is an user, so he have a login and password.
2)Execute ANY direct SQL in the database, because, even using ES as database access, ES will uses the same database user for all connection.

How to solve it :

Don't execute SQL statement. Send a command like "QueryCommand" and before execute it on ES/DataLayer context, pass it for the "Secure Layer", so we can do like it :

public void Authorization( QueryCommand cmd )
{
   string tableName = cmd.SchemaName;
   string affectedFields = cmd.Fields;
   QueryType queryType = cmd.QueryType;

AuthorizationLayer.Deman( tableName, affectedFields, queryType );
}

Rules :

1)Don't accept direct SQL Statement;
2)Join must be from a View and not direct from table;

Note: This approach isn't limitation for Stored Procedures.

The main key of it is :

1) My BO only access DataLayer(..Get, GetAll, Save, Update etc.. )
2) My QueryLayer only access my CommandLayer
3) My CommandLayer only acess my DataAccess Layer

Here's my test case code :

[TestMethod]
public void QuerySelectTest()
{
ISelectQuery<Customer> query = (ISelectQuery<Customer>)DataContext.CreateQuery<Customer>(QueryType.Select);
query.AddWhere(Customer.ColumnNames.CustomerID, "ALFKI");

Customer customer = query.Get();

Assert.IsNotNull(customer);
Assert.AreEqual(customer.CustomerID,"ALFKI");
Assert.AreEqual(customer.CompanyName, "Alfreds Futterkiste");
Assert.AreEqual(customer.ContactName, "Maria Anders");
Assert.IsNull(customer.Region);
}

/* Now the flow */

//DataContext
public static IQuery<T> CreateQuery<T>(QueryType queryType) where T : AbstractRecord<T>, new()
{
return Default.DataLayer.CreateQuery<T>(queryType);
}

//DataLayer ( SqlServerProvider )
public override IQuery<T> CreateQuery<T>(QueryType queryType)
{
return SqlQueryFactory.Create<T>(this, queryType);
}

//Factory
switch (queryType)
{
case QueryType.Select: return new SelectDataQuery<T>(provider, provider, queryType);
case QueryType.Insert:
case QueryType.Update: return new SqlDataQuery<T>(provider, provider, queryType);
default: throw new NotImplementedException();
}
}

//Now, Query.Get()
#region ISelectQuery<T> Members
public T Get()
{
return DataLayer.Get<T>(this);
}
#endregion

//DataLayer ( SqlServerProvider )

public virtual T Get<T>(IQuery query) where T : AbstractRecord<T>, new()
{
ExceptionHelper.ArgumentNullException(query, "query");

IRecordReader reader = query.ExecuteReader();
if (!reader.IsEmpty)
{
T obj = CreateObject<T>();
FetchObject<T>(obj, reader);
return obj;
}
else
{
return null;
}
}

//QueryLayer
public virtual IRecordReader ExecuteReader()
{
QueryCommand command = CreateCommand();
return _commandLayer.ExecuteReader(command);
}

//DataLayer(SqlProvider) implement ICommandLayer
public virtual IRecordReader ExecuteReader(QueryCommand command)
{
ExceptionHelper.ArgumentNullException(command, "command");
command.Prepare();
return command.ExecuteReader(_dataAccess);
}

//CommandLayer
public virtual IRecordReader ExecuteReader(IDataAccess dataAccess)
{
ExceptionHelper.ArgumentNullException(dataAccess, "dataAccess");
return dataAccess.ExecuteReader(this);
}

As you can see, my Command ONLY can access my Database when I pass it, so to turn it remotable easy, we can create a new provider WebServiceProvider that does it :

public override IRecordReader ExecuteReader(QueryCommand command)
{
/*the command must prepare all your data(i.e. table name, Efftected Fields and QueryType */
command.Prepare();

/* simple serialization of Command */

byte[] bytes = Nano.Mapper.IO.Serialization.SerializeBinary(command);
using (WCFMapperProviderProxy proxy = new WCFMapperProviderProxy())
{
bytes = proxy.ExecuteCommand(bytes);
}
return Nano.Mapper.IO.Serialization.DeserializeBinary<IRecordReader>(bytes);
}

// MyWebService method ONLY does it :

[Transaction(TransactionOption.Required)]
public byte[] ExecuteCommand(byte[] command)
{
QueryCommand queryCommand = Serialization.DeserializeBinary<QueryCommand>(command);
IRecordReader reader = queryCommand.ExecuteReader(DataContext.DefaultDataLayer.DataAccess);
return Serialization.SerializeBinary(reader);
}

1)As you can see, the Commands are executed on the Server DataAccess context.
2)You can easy encrypt, compress or create a custom serialization.
3)In the "ExecuteReader" of the command you can protect all access.
4)You can uses the TransactionScope class of .NET 2.0 without problem.

IRecordReader is my optimized IDataReader object.

Regards,
Alexnaldo Santos

RE: Is Direct SQL Statement Secure ?

StrataFrame Team — Thu, 22 Feb 2007 09:12:25 GMT

Oh, yes, LINQ will solve many of these problems. The Enterprise Server will be converted over to handle IQuery objects rather than passing through the SQL commands, and you'll be able to configure those query objects to only support "data" commands and not database maintenance commands (like dropping tables). So, yes, you get extra security in the additional logical layer because that layer becomes more configurable and therefore restrictable.

RE: Is Direct SQL Statement Secure ?

Ben Hayat — Wed, 21 Feb 2007 23:02:20 GMT

Ben, I'm sure you guys at SF have had a close look at the up coming LINQ. Will LINQ solve some these SQL security problems that exist now? Another words if we are talking to SQL server via LINQ layer (which internally still uses SQL), is our app safer with LINQ?

Thanks!

RE: Is Direct SQL Statement Secure ?

StrataFrame Team — Mon, 19 Feb 2007 10:11:55 GMT

The same rules apply to SQL Security from both the ES and directly connecting to SQL Server... if you allow direct connections, any one of your users with .NET (not just the ES SDK) can directly connect your database and execute any code. So, security SQL Server is your best bet. If you're worried about allowing users to execute code on your database, then turn off all permissions except EXECUTE and use only stored procedures.

There isn't a way to re-engineer the ES now... to work transparently with the current DbDataSourceItem system, it has to except and passthrough any authenticated command that is properly decrypted (by the PSK on both ends).

StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Is Direct SQL Statement Secure ?

Is Direct SQL Statement Secure ?

RE: Is Direct SQL Statement Secure ?

RE: Is Direct SQL Statement Secure ?

RE: Is Direct SQL Statement Secure ?