0

I'm trying to query a SQLite table by a blob column which is a SHA256 hash.

CREATE TABLE Articles (HASH BLOB(32) PRIMARY KEY, Article TEXT)

A query like this doesn't return anything:

SELECT * FROM Articles WHERE HASH = ?
-- the parameter is bound using sqlite3_bind_blob, not to use literals

I made this little Program in C# which creates the table and also inserts a row to try.

using System;
using System.Linq;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Security.Cryptography;

// using lib;

public class Program {

    static int Main(string[] args) 
    {
        // Console.WriteLine(sqlite3.LibVersion);

        byte[] qHash;
        string qArticle = "Lorem ipsum dolor sit amet";

        using (var hashAlgorithm = new SHA256CryptoServiceProvider ()) {

            byte[] bytes = System.Text.Encoding.UTF8.GetBytes(qArticle);

            qHash = hashAlgorithm.ComputeHash(bytes);
        }

        var con = new SQLiteConnection ("Data Source=test.sqlite");
        con.Open();

        try {
            var create = new SQLiteCommand ("CREATE TABLE Articles (HASH BLOB(32) PRIMARY KEY, Article TEXT)", con);
            create.ExecuteNonQuery();

            var insert = new SQLiteCommand ("INSERT INTO Articles (HASH, Article) VALUES (?, ?); SELECT last_insert_rowid();", con);

            var insertParam0 = new SQLiteParameter(DbType.Binary);
            insertParam0.Value = qHash;
            insert.Parameters.Add(insertParam0);

            var insertParam1 = new SQLiteParameter(DbType.String);
            insertParam1.Value = qArticle;
            insert.Parameters.Add(insertParam1);

            object insert_result = insert.ExecuteScalar();
            var insert_rowid = (long)insert_result; 

        } catch { } // silently fail if exists usually

        var query = new SQLiteCommand ("SELECT * FROM Articles WHERE HASH = @hash", con);

        var param = new SQLiteParameter ("@hash", DbType.Binary);
        param.Value = qHash;
        query.Parameters.Add(param);

        using (SQLiteDataReader reader = query.ExecuteReader()) {

            while (reader.NextResult()) {
                var hash = new byte[32];
                long hashLen = reader.GetBytes(0, 0, hash, 0, 32);

                string article = reader.GetString(1);

                Console.WriteLine("HASH: {0}; Article: {1}", 
                    hash == null ? null : BitConverter.ToString(hash).Replace("-", ""), 
                    article
                );
            }

        }
        con.Close();

        return 0;
    }

}

However the reader doesn't have a result. Is querying a BLOB column not supported at all?
Is something else bugging me?

4
  • Querying blobs should work just fine (example with blob literals). Did the insert work, i.e., does the DB contain that row? Commented Sep 13, 2014 at 9:39
  • Sure, the row exists; I don't want to use literals (which also don't work...), because of hex conversion, string concats, escaping etc. Commented Sep 13, 2014 at 9:43
  • HELL it works with reader.Read instead of .NextResult whyever. Should I close this Q? Commented Sep 13, 2014 at 9:57
  • @metadings Post that as an answer and accept it when you're allowed to. It'll help others in the future. Commented Sep 13, 2014 at 10:07

1 Answer 1

1

The problem here was solved by discovering that Read is used in a

while (reader.Read()) { }

loop, whereas NextResult is used in a

do { } while (reader.NextResult());

loop.

However the inital problem was, that in my real project I created the database using SQLiteStudio (SQLite 3.7.16.1) in the BLOB Hex Editor; that confused the System.Data.SQLite library (using SQLite 3.8.2).

Now by creating the database using the program itself solved also the initial issue

So said, don't mix SQLite versions, not even to quickly try something. I tried so hard, I've even started a UnmanagedLibrary with DllImport bindings for sqlite3_*, just to discover that this was not the problem.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.