I was talking with a buddy this morning and he brought up something I didn’t think to test before in SQLite. I knew from research that you had dynamically typed data columns in SQLite databases.

What I didn’t realize was that you can create table columns without a DataType set! Yup, you heard correct, no data type. I’m not sure why anyone would want to do this but it’s a feature.

The documentation for SQLite 3 databases states that if a DataType is not set the affinity is defaulted to BLOB. I have not tried testing this behavior but it makes sense since the database could be receiving any kind of data and hasn’t been told how to store it. You can review the documentation here.

Below you can see what I mean.

Create a table

Notice the column’s don’t have a DataType set!

Lets Look at the schema

SQLite table pragma

Conclusion

SQLite is a great database format that I love to utilize in my investigations. The loose constraints are an asset but they can also get you in to trouble. As you can see there are potential pitfalls to keep mind. When writing code check your data types. When creating a table check your syntax and ensure you have assigned data types to all the columns!