SQLite Forum

sqlite user authentication in c++ project
Login

sqlite user authentication in c++ project

(1) By EduadoZ (zzsedward) on 2020-06-10 03:52:31 [source]

I'm trying to build a sqlite database with user authentication enabled.
Following instruction from 
https://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt, 
attemps to read or write will fail if sqlite_user_authenticate returns error. 
However the program continued to create table and insert values to the database.
Does anyone know the problem? 
Appreciated!

How I implemented user_authentication:
I appended sqlite3userauth.h to the end of sqlite3.h
and userauth.c to the end of sqlite3.c
and compiled with 
gcc -DSQLITE_USER_AUTHENTICATION -c sqlite3.c -lpthread -ldl -o sqlite3.o
ar rcs libsqlite3.a sqlite3.o
so in my c++ project I link the libsqlite3.a as a lib.

Below is my test code:
int sqlite_user()
{
	sqlite3 *db;
	const char* dir = "sqluser.db";
	int rc=0;
	rc = sqlite3_open_v2(dir, &db,SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
	if(rc)
		fprintf(stderr, "Error Open Database %s\n", sqlite3_errmsg(db));
	else
		fprintf(stdout, "Database opended\n");

	const char* usrName = "zzw";
	const char* pw = "1111";

	rc = sqlite3_user_add(db, usrName, pw, 4, 1);
	if(rc!=SQLITE_OK)
		fprintf(stderr, "Error Create User %s\n", sqlite3_errmsg(db));
	else
		fprintf(stdout,"add user done\n");


	rc = sqlite3_user_authenticate(db, usrName, "1112", 4);
	if(rc!=SQLITE_OK)
		fprintf(stderr, "Error authenticate  %s\n", sqlite3_errmsg(db)); else
		fprintf(stdout, "user authorized\n");

	const char* tbName = "TESTTABLE("\
		"ID 	TEXT 	PRIMARY 	KEY 	NOT NULL,"\
		"CONTENT 			TEXT	NOT NULL);";
	
	string tablePrefix = "CREATE TABLE IF NOT EXISTS \t";
        ostringstream str1;
        str1<<tablePrefix<<tbName ;
        string sql = str1.str();

        int rc =0;
        rc = sqlite3_open(dir, &db);
        if(rc)
            fprintf(stderr,"Error Open Database %s\n", sqlite3_errmsg(db));
 
        char* msgErr;
           rc = sqlite3_exec(db, sql.c_str(), NULL, 0, &msgErr);
        if(rc!=SQLITE_OK)
        {
             fprintf(stderr,"Error Create Table %s\n", sqlite3_errmsg(db));
             sqlite3_free(msgErr);
         }
         else
         fprintf(stderr, "Table created successfully \n");


	char* sql;
	sql = "INSERT INTO TESTTABLE (ID, CONTENT)"\
		"VALUES('X1', 'SSS1');";
	char *errMsg=0;
	rc = sqlite3_exec(db, sql, 0,0,&errMsg);
	
	if(rc!=SQLITE_OK)
		fprintf(stderr, "error insert %s\n", sqlite3_errmsg(db));
	else 
		fprintf(stdout, "insert done\n");
	

	sqlite3_close(db);	
	

	return 0;
}

(2) By Stephan Beal (stephan) on 2020-06-10 19:53:24 in reply to 1 [link] [source]

attemps to read or write will fail if sqlite_user_authenticate returns error. However the program continued to create table and insert values to the database

Your function does not fail if authentication fails:

	if(rc!=SQLITE_OK)
		fprintf(stderr, "Error authenticate  %s\n", sqlite3_errmsg(db)); else
		fprintf(stdout, "user authorized\n");

It simply logs the error to stderr and continues.

Also, you are calling sqlite3_open_v2()/sqlite3_open() twice on the same handle without closing it between those calls. Don't do that: open the db once at the start of your app and close it once at the end. What you're doing will certainly cause a large memory leak.

(3) By EduadoZ (zzsedward) on 2020-06-11 00:54:42 in reply to 2 [link] [source]

Stephan,

Thanks a lot for your reply.

It simply logs the error to stderr and continues.
So does it mean that the authentication function doesn't really block people to access the database if password is wrong? Which means even if I write a mechanism inside the program to reject access, people can still access the content by direct reading from sqlite shell? Is there any real protection mechanism I can use?

(4) By Stephan Beal (stephan) on 2020-06-11 02:50:32 in reply to 3 [link] [source]

My understanding is that API is just a convenience implementation of app-level users, which are a common app feature which is tedious to implement. It is up to the app to figure out what each user may or may not do. sqlite itself has/does no sort of user authentication, but it does offer an encryption option with a license fee:

https://www.sqlite.org/see/doc/release/www/readme.wiki

(5) By Stephan Beal (stephan) on 2020-06-11 03:03:28 in reply to 4 [link] [source]

My understanding is that API is just a convenience implementation of app-level users,

That's incorrect. The API can apparently stop a user from writing to the db. My assumption about why it doesn't work for you is because you re-open the db with another connection (don't do th-the db is already opened). According to the docs, you have to call the authentication API after opening it. That said, i am unfamiliar with that API and am guessing based on a cursory glance at the docs.

(6) By EduadoZ (zzsedward) on 2020-06-11 05:36:01 in reply to 5 [link] [source]

I fixed the re-open issue, but it still happens. Authough authenticate returs an error, the folloing operation still works. I guess maybe my compilation has some problem? No idea But thanks anyway.

(7) By Warren Young (wyoung) on 2020-06-11 09:21:53 in reply to 6 [link] [source]

I fixed the re-open issue, but it still happens.

It sounds like you haven't addressed the key objection from Stephan, only the less serious secondary one, the memory leak.

I'll try to put it another way: unless you move the entire rest of your posted code into the "if...SQLITE_OK" branch, so that the only thing the "else" branch does is print an error before the function exits, your authorizer will of course not have any effect on the code flow.

However, all of that is a side issue. You speak of wishing to prevent modifications of the DB via the sqlite3 shell as well. None of what you're chasing will prevent that. The authorizer doesn't run when it isn't compiled into every SQLite based program that could possibly open your DB, including /usr/bin/sqlite3.

If you want to control who can access the DB, encryption is one answer.

Another is putting the DB on a server that you control access to, so that you can force accesses to go through the authorizer. This path is basically that of a huge number of web services: I don't get to edit your Gmail archive because Google controls access to those databases through their Gmail server-side web application code.

If you ship the raw DB to people, then only encryption can prevent modification. Physical access is total access.

(8) By EduadoZ (zzsedward) on 2020-06-12 08:20:25 in reply to 7 [link] [source]

Hi Warren,

Thanks for reply.

If you ship the raw DB to people, then only encryption can prevent modification. Physical access is total access.

I agree with you. It seems using authentication interface doesn't really controls access outside program, although I can write the code in a way only authentication returing OK can continue subsequent codes.
My point is if that's the case, what's the point of using this API? And if I understand correctly from the manual https://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt, that's also not how the API suppose to be, right?

(9) By Warren Young (wyoung) on 2020-06-12 09:21:37 in reply to 8 [link] [source]

what's the point of using this API?

Layering.

Let us say that you are creating a web app based on SQLite. Your web app has a login system, which controls who gets to do what with the underlying data, stored in SQLite. You could:

  1. Use whatever user authentication mechanism is built into your web app framework. Problem is, it probably won't know anything about SQLite, so it can't say, "This record is Bob's, and this record over here is Alice's, but Alice is an Admin, so she can modify Bob's records."

  2. Write your own user authentication framework.

  3. Start with this one, which embeds concepts of who can perform CRUD operations on the data down into the data layer, allowing the application code to largely ignore such things.

    I say "start with" because the extension as-given probably doesn't do everything you need it to, but it's often much easier to start with code that does most of what you want and then modify it to suit than it is to start from scratch.

    (Only "often" because that's dependent on how easy it is to understand and modify the existing code.)

Regardless of your choice, it is the access layer at the HTTP front end here that provides the enforcing power behind your authentication layer. If you took path #1 or #2 above but let anyone SSH into the server and modify the DB directly, they'd be just as bypassed as if you used this SQLite extension.

(10.1) By Keith Medcalf (kmedcalf) on 2020-06-12 16:34:47 edited from 10.0 in reply to 8 [link] [source]

I believe I recall a comment from Richard quite some time ago that this extension is no longer maintained.

It did work as you think it should work, once upon a time. Apparently that is no longer the case.

In any case, any such "thing" could be bypassed by simply using a version of SQLite that does not have that code installed, so it is not of much value. That is, the most appropriate description is that it would provide "authentication theater", and not actual meaningful authentication.