Encrypt SQLite Databases with SQLCipher

what is going on guys welcome back in this video today we're going to learn how to securely encrypt sqlite 3 or sqlite3 databases so let us get right into it [Music] all right so we're going to learn how to encrypt sqlite3 databases in this video today now regarding the pronunciation I know a lot of people like to call it sqlite3 I think both is fine I'm just going to stick with sqlite3 because that's how I pronounce it I'm sure there is a technically correct pronunciation if you do the research I'm just going to go with sqlite3 you can feel free to pronounce it however you like now why would we want to encrypt a database in the first place obviously because there is some sensitive information in it maybe some passwords maybe hashes of passwords maybe some company information some valuable information that we don't want our competitors to get something that you want to keep to yourself something that you want to keep secret and in sqlite 3 the database is essentially just a file so we have a simple file if we lose that file if someone else gets that file they can just open it up with the sqlite3 command line tool and they can see all the information that's in the database all the entries all the structure all the rows everything without having to enter a password and the tricky thing now is that the sqlite3 tool does not allow for encrypt option out of the box so the vanilla tool does not allow us to just encrypt the database we cannot just open the database and enter a password and say okay now you can only enter it with a password for this we would have to install or we have to install a third-party application an additional tool that allows us to encrypt the database and that then also allows us to use the encrypted database and this is what we're going to do in this video today we're going to learn how to do that and we're going to do all of this on a Linux system now I am using the windows subsystem for Linux so the Ubuntu system on Windows you can do that on Native Linux you can also use dwsl it's just simpler to do it on Linux because I'm sure it also works on Windows but the installation is a little bit more complicated on Linux we can just use the repositories the package manager and install everything we need without any problems now if you need help to install the windows subsystem for Linux I do have a video on this channel where I show you how to do that so you can watch that otherwise I'm just going to assume that you have this running either natively or you already have a Windows subsystem for Linux running so the first thing we want to do is we want to install the vanilla sqlite3 because what I want to show you here is first of all how to take something usually what you will do is you will not start a new database you already have maybe a database an sqlite3 database for example full of data and you want to take this database and turn it into an encrypted database now the third party tool that we're going to use is not able to just open the database and encrypt it we need to actually um we cannot just get an existing database and encrypt it we have to create a new one and we have to copy the data we have to transfer the data from an existing one to the new one so we're going to just install here sudo apt install on Ubuntu at least sqlite3 the basic tool in my case I already have this installed so you can see already the newest version so I can just go ahead and say sqlite3 and then the name of file so for example mydb.db and now I'm having this basic sqlite command line tool and here I can do some basic commands for example I can say create table people and I can say I want to have an ID which is an integer I want to have a name which is a text and I want to have an H which is also an integer just a basic table here and then I want to insert some values so insert into people then ID name H I want to insert the values 1 mic 30 then maybe um to oh now I pressed enter I hope that's not a problem uh to Sarah 40 and then maybe three Bop and 70. I hope that works there you go and now I can say select from select everything from people then we can see we have these entries now of course here we have a line break now it doesn't really matter but we have some data in the database now you can imagine a more complex database structure we can leave the tool by just pressing uh or by just typing dot Q then we leave the tool now I can open it up again sqlite3 mydb not DB select everything from people and this is the danger or not necessarily danger but this is what you have this is the situation that you have when you have an sqlite3 database you have this database you have the important information in it and everyone who gets the file can just open up sqlite3 uh and and the file can just type sqlite3 in the file into the command line and they can see all the information and there is no mechanism here to just encrypt this database so what we actually need to do is we need to install an additional tool and by the way we can also see the content here with hex dump so I can just say hex dump um Dash C and then mydb DB and we can see here even in the binary representation here even in hexes uh in the in the raw representation of this database we can see here Bob Sarah Mike we can see a lot of information just by looking at the bytes of this file so I don't even need the command line tool and we don't want to have this of course so what we're going to install here is an additional tool I'm going to say sudo apt install SQL Cipher and in my case again I already have this tool installed but this tool is going to allow us to basically do the same thing that we can do or all the same things that we can do with sqli 3 but we're also going to be able to use encryption so um first of all what we want to do is we want to get the existing database that we already have and we want to export uh everything we want to export the creation script or we want to generate a creation script so that we can reproduce everything we want to have all the tables all the connections all the entries and how we do that is we open up the database with uh the basic sqlite 3 tool so mighty bdb and here what I do now is I say dot out to specify that whatever comes next is going to be written into a file so dot out I'm going to call this create underscorescript.sql and then I'm going to just say dot dump and Dot dump essentially creates or generates a create script in an insert script for all the stuff that is in the database so I can type dot Q here I can now cat so I can show the create script SQL and you can see here uh foreign keys off begin transaction create table insert into and then commit so you can also see here the line break with Sarah um but yeah you can see this is the script that we would need to reproduce the database to basically create the same database so we're going to use that script now in SQL Cipher to create an encrypted version of that database so we can go ahead and say SQL Cipher and I will create now a new database I'm going to call it inc.db so encrypted.db and the first thing we want to enter here now is a pragma statement so we want to say pragma key equals and then some password this is going to be the password that we're going to use for encryption or for decryption also so we're going to call this now password for example you can call it one two three of course if you use this on an actual database that's important to you use a secure password I also have a video on this channel on how to choose a secure password I think I have it on the channel if I didn't remove it um so I set this key here and then what I do is I basically just read in the SQL statement so I just say read um so dot read to read then in SQL file create script SQL and then dot queue to basically break out or to basically quit um and then if I load this again so first of all if I use just sqli3 to open up inkdb and then I say for example here select everything from people we can see here it says file is not a database that's good because now it's not usable I can also call a hex dump Dash C onto the encrypted database and you can see there's nothing that we can understand here this is just bytes right so nothing nothing that shows any information but if I now go ahead and I say SQL Cipher ANC dot DB and if I now go select everything from people I'm still not going to get anything so file is encrypted or it's not a database but if I now say pragma key equals password like this and then I repeat the statement now you can see I have the data so the database is now encrypted I can decrypt it using the same password and that is how that works now what you can also do is I'm going to now repeat this here I can say SQL Cipher Inc 2db I'm going to show you a different way to copy the information what we can do here is we can say pragma key equals password again and now instead of loading an SQL script we can also just attach the other database so we can say attach database data base uh what was the name mydb.db I think that was correct as mydb for example so now the database file is attached as mydb we have this variable and now I can just go and say create table people ID int um name text h int I can do it like that and then I can say insert into people select everything from my DB and I can also specify for example where ID is not two for example no such table might be oh mydb dot people so now if I go and say select everything from people you will see that Mike and Bob are in the database Sarah is not so I can also do it like that if I want to but at the end of the day it's going to be encrypted that's what's important here so I can just use SQL Cipher to encrypt um to encrypt this database now and the next thing we need to look at let me just open this up here on my second screen because I have some prepared code here uh the next thing we want to do is we want to automate this in Python so what would be the wrong way to do it let me just open up here with neovim a new python file I'm going to call this wrong py we can just use the core python module sqlite3 and we can say here the connection is equal to sqlite3 dot connect and um the one that works is the mighty bdb so I can just say here cursor equals connection dot cursor and then cursor.execute select everything from people print cursor fetch all to get all the rows and then I can commit the connection even though I don't know if that's necessary since we don't change anything we close the cursor we close the connection uh and that's basically how this works right so I can go ahead now I can say Python 3 wrong py this works for the unencrypted database of course it's not going to work now for the encrypted database so if I go ahead and I say encrypted DB this is not going to work you can see file is not a database so what we need to do here is also we need to install um the python module so first of all on Linux we need to say sudo apt install lip SQL Cipher Dash def so this is something we need to install on the system in my case already installed and in addition to that we also need to install for pip so pip 3 install Pi SQL Cipher 3. there you go and now what we need to do here I'm going to basically copy the wrong py file to correct py I'm going to open up correct py now we're going to replace sqlite3 here by saying um from PI SQL Cipher three we're going to import DB API 2 as sqlite now the Alias is option optional you don't need to do that but now we can just replace sqlite3 here by sqlite the rest stays the same and of course one thing that we need to do first is we need to say or actually let me show you that this doesn't work right out of the box you can see here file is encrypted or not a database but if I now add an additional execute statement here where I say pragma key equals password like that then you can see that I actually get the content of the database so this is how you can also use this encrypted database in Python with that module and of course this makes sense you don't want to have of course the password here in clear text you either want to have it as a user input or you want to have it in the environment or you want to have it somewhere but not just in the code clear text um but this is how you do that right so you would just encrypt the database you would copy the existing database code you would generate this create statement script you would load it into the new encrypted database you would encrypt the database and then you would have to decrypt it every time in order to use it but now if someone else gets this file as I already showed you if someone else gets this file they can do nothing with it they cannot open it with any tool rmdb they cannot open it with hexdom they cannot see any information here they cannot open it with sqlite3 they cannot even open it with SQL Cipher if they don't have the key so if you lose the file if someone gets the file that's not too much of a problem uh compared to a basically a basic unencrypted database file so that's it for today's video I hope you enjoyed it and hope you learned something if so let me know by hitting the like button and leaving a comment in the comment section down below and of course don't forget to subscribe to this Channel and hit the notification Bell to not miss a single future video for free other than that thank you much for watching see you next video and bye foreign [Music]

Channel: NeuralNine

Views: 10,480

Keywords: python, sql, sqlite, sqlite3, encrypt sqlite3, encrypt sqlite database, encryption, encrypt sqlite3 database, sqlcipher, python sqlite3 encryption, python pysqlcipher3, pysqlcipher3

Id: 8PARZE2aTOQ

Length: 14min 57sec (897 seconds)

Published: Mon Mar 27 2023