Yes, Simple.Data protects against SQL Injection

Several people who weren’t following the show at the inception of Simple.Data have asked whether it does anything to prevent SQL Injection attacks. Those of you who remember the start of the project will understand why I’m kind of amused by the question.

I originally built Simple.Data as a proof-of-concept when there was a big web-scuffle over what was then called Microsoft.Data (now WebMatrix.Data). David Fowler, a developer I have since come to respect greatly, posted an example on his blog which used concatenated SQL, and everybody lined up to tear him a new one. I’m a big fan of code over cockfights, so I had a go at implementing a simple data access library which made SQL Injection impossible by eliminating SQL strings. Thus Simple.Data. You told it what you wanted, and provided the necessary values, and then it wrapped them safely up in parameters and executed them. I stuck it on GitHub, and posted about it on my blog.

(In fact, the first version did allow you to execute SQL strings, much like Dapper and Massive, because the functionality was extremely limited otherwise. As I’ve iterated on it, I’ve been able to expose nearly all the required functionality using the dynamic approach, so the only way you can execute SQL directly now is by getting an IDbConnection object from the AdoAdapter and using ADO.NET against it.)

Comments

  1. Bobby Johnson says:

    code over cockfights might just have to be my new mantra… 8)

  2. Isn’t there a way to do something like:
    db.Execute(“update Stock set Current = Current – 1 where ProductId = ?”, productId)
    ?

    I’m trying to reproduce a few thousands bulk insert in SQLite. In raw ADO.NET it takes less then a second. with:

    using (DbConnection cnn = DbProviderFactory.CreateConnection())
    {
    cnn.ConnectionString = ConnectionString;
    cnn.Open();

    using (DbTransaction dbTrans = cnn.BeginTransaction())
    {
    using (DbCommand cmd = cnn.CreateCommand())
    {
    cmd.CommandText = “INSERT OR IGNORE INTO Keywords(Name) VALUES(?) “;
    DbParameter Name = cmd.CreateParameter();
    cmd.Parameters.Add(Name);

    foreach (string[] row in keywords)
    {
    Name.Value = row[0];

    cmd.ExecuteNonQuery();
    }
    }
    dbTrans.Commit();
    }
    } // end DbConnection

    When I try to use:
    db.Keywords.Insert(parsedKeywords);
    It takes few minutes. Not good…

  3. I’ve changed by code to:

    var file = “KeywordsSimpleData.db”;
    var pr = ProviderHelper.GetProviderByFilename(file);
    var db = Database.OpenFile(file);
    Database.TraceLevel = TraceLevel.Off;

    // start using shared adapter
    DbConnection connection = DbProviderFactories.GetFactory(“System.Data.SQLite”).CreateConnection();
    ((AdoAdapter)db.GetAdapter()).UseSharedConnection(connection);
    connection.ConnectionString = “Data Source=KeywordsSimpleData.db”;

    var keywords = db.Keywords.All();
    foreach (var keyword in keywords)
    Console.WriteLine(keyword.Name);

    List parsedKeywords = GetKeywords();
    using (var transaction = db.BeginTransaction())
    {
    db.Keywords.Insert(parsedKeywords);
    transaction.Commit();
    }

    // stop using shared adapter
    ((AdoAdapter)db.GetAdapter()).StopUsingSharedConnection();

    And not it works near 30x times faster! Amazing! You should consider not re-opening connection for bulk operation I think.

    • That’s interesting. Bulk inserts are supposed to use a persistent connection and a prepared command, but from what you’re saying that’s not the case. Further investigation is required.

      • I’m using Simple.Data.Sqlite 0.12.2.1 and Simple.Data 0.12.2.1

        I will continue using this 2 dirty hacks:

        1. ((AdoAdapter)db.GetAdapter()).UseSharedConnection(connection);

        2. Specifying both “var db = Database.OpenFile(file);”
        and
        ((AdoAdapter)db.GetAdapter()).UseSharedConnection(connection);
        connection.ConnectionString = “Data Source=KeywordsSimpleData.db”;
        Because “var db = Database.OpenConnection(“Data Source=KeywordsSimpleData.db”); ” can’t find the the right DB provider and throws the “Multiple ADO Providers found; specify provider name or remove unwanted assemblies.” Exception.

  4. It appears that Simple.Data.Sqlite was referencing an old version of Simple.Data.Ado which didn’t have the inserts optimized. Should be fixed now.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,750 other followers

%d bloggers like this: