1

I am working on embedded system and the device has linux kernel with sqlite database. Wanted to know if the sqlite database can be partitioned with secure and normal partitions.

How can the encryption be achieved for sqlite database file in linux.

Karmastan
  • 5,618
  • 18
  • 24
Madhurima
  • 11
  • 1
  • 2

3 Answers3

1

Maybe I am too late to answer this question, but I was facing this issue from couple of days and couldn't find any solid solution online. I have found solution hence I am sharing it.

//Steps to make sqlite database authenticated

  1. download sqlite3 amalgamation zip file

  2. unzip the file. The file should contain shell.c, sqlite3.c, sqlite3.h, sqlite3ext.h

  3. click on find the link here

3a. Open userauth.c and copy the entire code and paste it at the end of your sqlite3.c file.

3b. Open sqlite3userauth.h and copy the entire code and pase it at the end of your sqlite3.h file.

  1. create a output file for executing the command in shell command: gcc -o sqlite3Exe shell.c sqlite3.c -DSQLITE_USER_AUTHENTICATION -ldl -lpthread

4a. Youll get error no such file "sqlite3userauth.h" in your shell.c file: solution: go to that file and comment th line.(this is because youve already included the necessary code when you copied sqlite3auth.h into sqlite3.h)

4b. Test your output file by running ./sqlite3Exe (this is the name youve given to the output file generated in previous step). you'll get sqlite console.

4c. Create a database and on the authentication flag:

command1: .open dbname.db

command2: .auth on

command3: .exit//command 3 is optional

  1. Building the library 5a: creating object file //Compiling sqlite3.c to create object after appending our new code

command: gcc -o sqlite3.o -c sqlite3.c -DSQLITE_USER_AUTHENTICATION

With this command, we generate object file which we can use to compile our c file.

  1. Create c file to authenticate your database:

    //authUser.c
 #include "stdio.h"
 #include "stdlib.h"
 #include "sqlite3.h"
 int main(int argc,char * argv[]){
     int a = 10;
     int rtn, rtn2;
     sqlite3 *db;
     char *sql, *zErMsg;
     rtn = sqlite3_open("dbname.db", &db);
     rtn = sqlite3_user_add(db,"username","password",2, 1);//last but   one param is for number of bytes for password, last param is for weather the user is admin or not
     if(rtn){
         fprintf(stderr, "Can't open database: %s\n",     sqlite3_errmsg(db));
           return(0);
         }else{
           fprintf(stderr, "Protected database successfully\n");
         }
         sqlite3_close(db);
         return 0;
 }

  2. Compiling the program //Compiling the program command1: gcc authUser.c sqlite3.o -lpthread -ldl command2: ./a.out //Output:protected database successfully

  3. create c file to create table if the user is authenticated

    //createTable.c
 #include "stdio.h"
 #include "stdlib.h"
 #include "sqlite3.h"
 static int callback(void *NotUsed, int argc, char **argv, char **azColName){
    int i;
    for(i=0; i less then argc; i++){
       printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
 }
 int main(int argc,char * argv[]){
     int a = 10;
     int rtn, rtn2;
     sqlite3 *db;
     char *sql, *zErMsg;
     rtn = sqlite3_open("dbname.db", &db);
     rtn = sqlite3_user_authenticate(db, "user","password",2);
     if(rtn){
         fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
           return(0);
         }else{
           fprintf(stderr, "Opened database successfully\n");
         }
     sql = "create table newtable(id int not null primary key, name varchar(100) not null)";
     //sql = "insert into newtable values(5, 'ishwar')";
     rtn = sqlite3_exec(db, sql, callback, 0, &zErMsg);
     if(rtn != SQLITE_OK){
         sqlite3_free(zErMsg);
     }else{
         fprintf(stdout, "Table created successfully \n");
         //fprintf(stdout, "inserted successfully \n");
     }
         sqlite3_close(db);
         return 0;
 }
    `

  4. compiling the program //Compiling the program

command1: gcc createTable.c sqlite3.o -lpthread -ldl

command2: ./a.out //Output:Table created successfully

  1. Create c file to add values in table

from the previous code, you can see two sql variable and two fprintf inside else, now uncomment the commented line and comment the other one. and runt the same command as above output: Inserted successfully

And youre done, try experimenting with the code, change the values of sqlite3_user_authenticate function you wont be able to do these operations,at max you may be able to open database(when you comment the sqlite3_user_authenticate functon.nothing else)

  1. Testing it with shell

Run the command: ./sqlite3Exe (the output file we created in step 4)

command1: .open dbname.db

command2: .tables //you should get error, user_auth

Thank you(please feel free to mail me in case of any problem: ishwar.rimal@gmail.com)

eyllanesc
  • 235,170
  • 19
  • 170
  • 241
Ishwar Rimal
  • 1,071
  • 11
  • 19
  • 1
    I followed all the steps and authentication works in the code but the database is still accessible from sqlite3Exe without authentication, i.e in step 11 i get the tables. Any idea why ? – sotiris Jun 02 '20 at 14:38
  • @ishwar-rimal I get `Can't open database: no such function: sqlite_crypt` on `rtn = sqlite3_user_add(db,"username","password",2, 1);` what does it mean? – NewUser Aug 31 '20 at 11:52
  • @sotiris Do you also get `Can't open database: no such function: sqlite_crypt`? how did you fix it? – NewUser Aug 31 '20 at 12:03
0

David Segleau here, Director of Product Management for Berkeley DB.

The recent 5.1 release of Oracle Berkeley DB (5.1.7) integrates the Berkeley DB encryption feature with the SQLite-based SQL API. You can read about it here.

dsegleau
  • 1,942
  • 9
  • 13
0

for encryption to be achieved with SQLite, you need to license some extensions from the SQLite author.

http://www.sqlite.org/support.html

Cesar A. Rivas
  • 1,355
  • 1
  • 10
  • 13