Does anybody here know how one can password/encrypt SQL ITE database in C’++.
The strange thing is that Google and most other pipular fora have not been able to help.

Are you encrypting a string or the database itself?

Steps:
a. Open the sqlite database file in read lock mode as a temporary file(e.g appdb.db.tmp) .
b. Modify the data in the database.
c. With the file handle you have opened, encrypt the file.
d. Write the encypted file to app.db and delete app.db.tmp.

seunthomas:
Steps:
a. Open the sqlite database file in read lock mode as a temporary file(e.g appdb.db.tmp) .
b. Modify the data in the database.
c. With the file handle you have opened, encrypt the file.
d. Write the encypted file to app.db and delete app.db.tmp.

Now this is funny, I have been programming in C++ for years but do not think I know how to open a file in read lock mode as temporary file as u indicated here. Is the highlighted operation an SQLite operation or a normal c++ file operation or a Win32/MFC operation? By the way I am using MFC for this app and it means I am programming for windows.

If it is an SQLite3 operation I could quickly check the manual online and get the details, but if it a c++ file operation, I am afraid I don't know it and I will really appreciate it if you can give me a brief explantion on that. I assure that I will quickly get the gist if you give me a brief explanation. Even though I am progrmming with MFC, I can use any C/C++ construct that I desire.

The lesson, I am learning today is never to underrate nairaland. Many popular glabally acclaimed forum that I belong to have failed me. So also has google on this. Talk of something being in the pocket of your sokoto....

Please, friend kindly help me with more detailed info as requested.



After thought:
Since you used the word "handle" I am assuming it a win32/MFC file open operation. I am checking that up now. This doesn't however mean you should no longer give the requested additional detail.

gimakon:
Are you encrypting a string or the database itself?

Actually, because I couldn't encrypt that database it self or password it, I am at the moment, encrypting the strings as I learnt through google search, but any body who knows what to do can see view the schema of my database, now that is a large security hole that I have been worried about.

I wish to encrypt the entire database.

gmus:



Now this is funny, I have been programming in C++ for years but do not think I know how to open a file in read lock mode as temporary file as u indicated here. Is the highlighted operation an SQLite operation or a normal c++ file operation or a Win32/MFC operation? By the way I am using MFC for this app and it means I am programming for windows.

If it is an SQLite3 operation I could quickly check the manual online and get the details, but if it a c++ file operation, I am afraid I don't know it and I will really appreciate it if you can give me a brief explantion on that. I assure that I will quickly get the gist if you give me a brief explanation. Even though I am progrmming with MFC, I can use any C/C++ construct that I desire.

The lesson, I am learning today is never to underrate nairaland. Many popular glabally acclaimed forum that I belong to have failed me. So also has google on this. Talk of something being in the pocket of your sokoto....

Please, friend kindly help me with more detailed info as requested.

http://stackoverflow.com/questions/853805/locking-files-using-c-on-windows
The idea of locking the file is so that no one else can access it while you use it.
Also depend on the library you can also lock the db from sqlite. https://www.sqlite.org/lockingv3.html But since you want to encrypt the database this wont be appropriate.

seunthomas:


http://stackoverflow.com/questions/853805/locking-files-using-c-on-windows
The idea of locking the file is so that no one else can access it while you use it.
Also depend on the library you can also lock the db from sqlite. https://www.sqlite.org/lockingv3.html But since you want to encrypt the database this wont be appropriate.

It is not locking files that is strange to me, it is opening it in the temporary mode using the extension you gave that is strange to me.

gmus:


It is not locking files that is strange to me, it is opening it in the temporary mode using the extension you gave that is strange to me.

Dude you are not a learner but let me break it down to you. In most sqlite libraries you have an option to specify the filename for the db. Now the idea of locking the file is so no one can access it while your are making edits. The temp file is so your application thread can still make edits like do an insert. The default file name of sqlite is .db and in some cases no extension. Its simple. You just create a function that does this for you. I cant give u the details.

char[] openFileWithLock(char *filename){

//do your implementation here
//decrypt the file
return filename(so your application can work with the file(.tmp)
}
//your file would be converted back to .db or whatever extension
void releaseFileLock(){
//encrypt the file
}
void decryptFile(char *filename){
//decrypt the file
}
void encryptFile(char *filename){
//encrypt the file
}

seunthomas:


http://stackoverflow.com/questions/853805/locking-files-using-c-on-windows
The idea of locking the file is so that no one else can access it while you use it.
Also depend on the library you can also lock the db from sqlite. https://www.sqlite.org/lockingv3.html But since you want to encrypt the database this wont be appropriate.

I suppose dt this what you mean

gmus:




I suppose dt this what you mean

add me on skype:hackiprog
I have a few minutes.

seunthomas:


http://stackoverflow.com/questions/853805/locking-files-using-c-on-windowshttps://msdn.microsoft.com/en-us/library/windows/desktop/aa363858(v=vs.85).aspx#caching_behavior
The idea of locking the file is so that no one else can access it while you use it.
Also depend on the library you can also lock the db from sqlite. https://www.sqlite.org/lockingv3.html But since you want to encrypt the database this wont be appropriate.

I suppose dt this what you mean

https://msdn.microsoft.com/en-us/library/windows/desktop/aa363858(v=vs.85).aspx#caching_behavior

gmus:


Actually, because I couldn't encrypt that database it self or password it, I am at the moment, encrypting the strings as I learnt through google search, but any body who knows what to do can see view the schema of my database, now that is a large security hole that I have been worried about.

I wish to encrypt the entire database.

You mean hackers? Hmm.. have you tried doing something like this
The only way someone can break in to your server is when you use things like sha1, mysql,or some simple encryption system to do something

I remember a project a friend from Russian federation did, he didn't use a simple encryption system, he used serpent encryption
That technique he also embedded into some 256 bit encryption standard system.
If I could still remember, he did it inside his code like void() whatever so he didn't have to do inside the database

What I'm saying is this, hence the database of sql lite may not be too secure per say and porous to vulnerable attacks from hackers why don't u switch to say mysql or oracle and learn how to apply a more serious technique of encryption in your code not in your database

Look up the code / methods on how to do this from youtube

Hope I helped

seunthomas:

Dude you are not a learner but let me break it down to you. In most sqlite libraries you have an option to specify the filename for the db. Now the idea of locking the file is so no one can access it while your are making edits. The temp file is so your application thread can still make edits like do an insert. The default file name of sqlite is .db and in some cases no extension. Its simple. You just create a function that does this for you. I cant give u the details.

char[] openFileWithLock(char *filename){

//do your implementation here
//decrypt the file
return filename(so your application can work with the file(.tmp)
}
//your file would be converted back to .db or whatever extension
void releaseFileLock(){
//encrypt the file
}
void decryptFile(char *filename){
//decrypt the file
}
void encryptFile(char *filename){
//encrypt the file
}

I understand what u are saying perfectly well now. I actually wrote a similar implementation dt did not work. I use CppSQLite3 C++ wrapper for sqlite. Apart from d fact dt i used a mutex to ensure read lock and d fact that i used another file with a name formed usung the name of d actual db file, my implentation and yours are identical. However each time i attempt to copy d (now) re-encrypted file back to d database file, an exception is thrown.

seunthomas:

add me on skype:hackiprog
I have a few minutes.

I dont have a skype account. I'll have to set up one now. There is power failure here at my end and i have switched to my phone.I'll see if my phone can skype. I'll download skype now. just a minute or two.

gimakon:


You mean hackers? Hmm.. have you tried doing something like this
The only way someone can break in to your server is when you use things like sha1, mysql,or some simple encryption system to do something

I remember a project a friend from Russian federation did, he didn't use a simple encryption system, he used serpent encryption
That technique he also embedded into some 256 bit encryption standard system.
If I could still remember, he did it inside his code like void() whatever so he didn't have to do inside the database

What I'm saying is this, hence the database of sql lite may not be too secure per say and porous to vulnerable attacks from hackers why don't u switch to say mysql or oracle and learn how to apply a more serious technique of encryption in your code not in your database

Look up the code / methods on how to do this from youtube

Hope I helped

D problem is that mysl is not embedded. Using mysql means users must have mysql installed on their computer. That will defeat an important purpose. users are not even supposed ti know dt there is a database bcouse d app has it own file type extension.

gmus:




D problem is that mysl is not embedded. Using mysql means users must have mysql installed on their computer. That will defeat an important purpose. users are not even supposed ti know dt there is a database bcouse d app has it own file type extension.

Then make it remote, let your application connect to the database remotely. It should use probably the same remote string to do such.

gimakon:


Then make it remote, let your application connect to the database remotely. It should use probably the same remote string to do such.

The app is such dt users can either use it on a standalone computer or on a local area nework or the internet. users can choose any preferred use mode at any time. for the internet use there is no security concern it is remote. d standalone computer use require an embedded datbase. it should such dt users can use it whether they have a network access or not.

gmus:


The app is such dt users can either use it on a standalone computer or on a local area nework or the internet. users can choose any preferred use mode at any time. for the internet use there is no security concern it is remote. d standalone computer use require an embedded datbase. it should such dt users can use it whether they have a network access or not.

We are saying the same thing
What you need is to make the backend database a remote one, when you talk about embedded, you do NOT embed a remote database, you use a connection string to create an up link to the server you are connecting to.
Hence you might have something like a virtual server like Microsoft server 2000 and deploy your mysql server into it then link to the server from your code.
When you finish creating your. Exe file, together with your mysql connector or even mssql database connector you can link to your database without problem.

Take it slow, I suggest you work on your skills very well before you pick on things like this.

It is obvious that u don't understand me. The app uses client/server programs to communicate with server versions of the app installed on a network server computer. So the server versions of the app can very well use embedded database since it communicate with its clients via client/server programs.

gimakon:


We are saying the same thing
What you need is to make the backend database a remote one, when you talk about embedded, you do NOT embed a remote database, you use a connection string to create an up link to the server you are connecting to.
Hence you might have something like a virtual server like Microsoft server 2000 and deploy your mysql server into it then link to the server from your code.
When you finish creating your. Exe file, together with your mysql connector or even mssql database connector you can link to your database without problem.

Take it slow, I suggest you work on your skills very well before you pick on things like this.

But , Initially wanted to use the approach you stated above, but I had a feeling it will be less tedious to use a client/sever approach. It will be just a matter of tweaking the already existing code used for connecting to the local database.

gmus:


But , Initially wanted to use the approach you stated above, but I had a feeling it will be less tedious to use a client/sever approach. It will be just a matter of tweaking the already existing code used for connecting to the local database.

Then do what you like. I gave you something that's quite easy to solve your problems.
It's ok, suit yourself

You are still on this job. Haba by now you should have finished.

seunthomas:
You are still on this job. Haba by now you should have finished.

I gave him an option to use. He insisted he wants to make somethings extremely difficult for himself, so I see am say, maybe he wants to make his work hard. I decided to allow him

seunthomas:
You are still on this job. Haba by now you should have finished.

I have finished, I am presently debugging it.

I just simply modified the previously written implementation code.It is part of a very big app that has over 500 dlls. so the little change will require a little more debugging.

Oh is is because of my discussion with gimakon that you asked. I am just try to explain why I choose embedded data bays over non-embedded ones.

gimakon:


I gave him an option to use. He insisted he wants to make somethings extremely difficult for himself, so I see am say, maybe he wants to make his work hard. I decided to allow him

Ok. Let me give you more detailed explanation for my seeming unwise decision.

The app I am working on is a composite app that is all of a word processor, spreadsheet and drawing app. I started writing it in January last year and I am yet to finish. Users pick database values from menu items and data gets into database from interaction with dialogue boxes.

50% of the codes has to do interraction with database.

The vast majority of the user are expected to be people with little or no compute literacy,so designing the app in such a way that all they need do is buy and install is the objective. There is expected to be no need of installation of any other app or need to do any other setting( apart from selecting the use mode which could be desktop, network or internet and setting the server ip address , if the use mode is network).

I believe understanding ones market and factoring their level of competency and putting it into consideration during development is important to the success of an app in the market.If you say I am making my work more difficult, you are right. The making plan for the app excludes sending people to setup the app for the user or trainging them to use it. The marketing philosophy is similar to saying " Wash and wear". In this case "buy and install."

But the real truth is that I am actually making my work easier.

for every dll in the app I am expected to write something like:

if(use == Desktop)
{
}
else if(use == Network)
{
}
else if(use == Internet)
{
}

for now I am writind codes for the case
Use == Desktop

Once I am done I will start writing for the case use == to network and use eqaul to desktop.

Because some users will choose to use the app indefinitely on a standalone computer, there is need for a local database and since users are not expected to install additional app, the database must be embedded. ( Actually I had started using sql server compact nut discontinue coding for it for some reasons).

Now, as you probably know, differant database management system require different programming approach. The approach for sql server is differant from the appraoch for mysql sever which is also different from the approach for oracle.

Suppose I use your appraoch that means I will have to start writing (almost entirely)fresh codes for the network and internert use case( which incidentally require identical codes because the only different will be the server address.)

But if , as I plan I make the database for the Network case embedded, all do is spend possible a week to write a wrapper class for sqlite that will handle database access and interraction. This clause will behind the scene use client/server programs. It will simply send queries to the server and receive the result from the server or send data manipulation queries and associated data to server.

Having done that, all I need to do next is to copy the content of use == desktop case into the other two case and just tell the compiler to change the wrapper class name from the one for the desktop to the one for the network, then job over.

But I must admit that save for the the peculiarities of this app, your approach is actually easier.



Post-Addition:
If you know a class or way of using the same codes for different database system with the difference being only in the connection string, please let me know. ( I believe you know that I am talking C++ here.)

gmus:


Ok. Let me give you more detailed explanation for my seeming unwise decision.

The app I am working on is a composite app that is all of a word processor, spreadsheet and drawing app. I started writing it in January last year and I am yet to finish. Users pick database values from menu items and data gets into database from interaction with dialogue boxes.

50% of the codes has to do interraction with database.

The vast majority of the user are expected to be people with little or no compute literacy,so designing the app in such a way that all they need do is buy and install is the objective. There is expected to be no need of installation of any other app or need to do any other setting( apart from selecting the use mode which could be desktop, network or internet and setting the server ip address , if the use mode is network).

I believe understanding ones market and factoring their level of competency and putting it into consideration during development is important to the success of an app in the market.If you say I am making my work more difficult, you are right. The making plan for the app excludes sending people to setup the app for the user or trainging them to use it. The marketing philosophy is similar to saying " Wash and wear". In this case "buy and install."

But the real truth is that I am actually making my work easier.

for every dll in the app I am expected to write something like:

if(use == Desktop)
{
}
else if(use == Network)
{
}
else if(use == Internet)
{
}

for now I am writind codes for the case
Use == Desktop

Once I am done I will start writing for the case use == to network and use eqaul to desktop.

Because some users will choose to use the app indefinitely on a standalone computer, there is need for a local database and since users are not expected to install additional app, the database must be embedded. ( Actually I had started using sql server compact nut discontinue coding for it for some reasons).

Now, as you probably know, differant database management system require different programming approach. The approach for sql server is differant from the appraoch for mysql sever which is also different from the approach for oracle.

Suppose I use your appraoch that means I will have to start writing (almost entirely)fresh codes for the network and internert use case( which incidentally require identical codes because the only different will be the server address.)

But if , as I plan I make the database for the Network case embedded, all do is spend possible a week to write a wrapper class for sqlite that will handle database access and interraction. This clause will behind the scene use client/server programs. It will simply send queries to the server and receive the result from the server or send data manipulation queries and associated data to server.

Having done that, all I need to do next is to copy the content of use == desktop case into the other two case and just tell the compiler to change the wrapper class name from the one for the desktop to the one for the network, then job over.

But I must admit that save for the the peculiarities of this app, your approach is actually easier.

That's a long note.
One question, on each if these approaches, is the database gonna be seen?

gimakon:


That's a long note.
One question, on each if these approaches, is the database gonna be seen?

The database will not be seen. It is to be hidden from all users. They just know intuitively that there is a database, but they can't( at least that is the intention) find it.

gmus:


The database will not be seen. It is to be hidden from all users. They just know intuitively that there is a database, but they can't( at least that is the intention) find it.

Your app is mainly based on use case system. What ever they do, the database is meant to be hidden so that it cannot be altered any way, any form. Now you are able to see what I have been saying yourself.

Whether desktop,database, network as you specified, that is FRONTEND, backend whatever happens doesn't concern the user, that's why I said you make your application sort of remote, for these reasons

. can be accessed from anywhere by the admin, hence there is a leak, he could fix it
. It would even save you cash transport, they call you, say what ever the problem is, once logged on to a server, it could be corrected
. SQL injection is impossible you design it, and have it deployed, only the developer knows where the db is, unauthorised entry is almost impossible unless you give the access to hackers yourself .

And some other reasons, ethics of Microsoft won't even support the "embedding " of database and it doesn't show a professional job, unless you are ready to suffer leaks and maybe go to jail later when they see how incompetent the job is ,they might feel u gauged their money.

Think about it .......

gimakon:


Your app is mainly based on use case system. What ever they do, the database is meant to be hidden so that it cannot be altered any way, any form. Now you are able to see what I have been saying yourself.

Whether desktop,database, network as you specified, that is FRONTEND, backend whatever happens doesn't concern the user, that's why I said you make your application sort of remote, for these reasons

. can be accessed from anywhere by the admin, hence there is a leak, he could fix it
. It would even save you cash transport, they call you, say what ever the problem is, once logged on to a server, it could be corrected
. SQL injection is impossible you design it, and have it deployed, only the developer knows where the db is, unauthorised entry is almost impossible unless you give the access to hackers yourself .

And some other reasons, ethics of Microsoft won't even support the "embedding " of database and it doesn't show a professional job, unless you are ready to suffer leaks and maybe go to jail later when they see how incompetent the job is ,they might feel u gauged their money.

Think about it .....

First, microsoft does encourage embedding. They wrote sql server compact and encourage pple to use it for their desktop apps and website.

mozilla firefoce use it. i also on sqlite website of facebook engineers reportg a bug meang face book use it.

Even if d datbase is discovered it will be secure because it uses aes to encrypt d entire database file and additionally the string content.

The administrator will be in d dark as much as any one else.

for d network use, d datbase is still remote. only d person who has access to server computer, supposedly d administrator, who has is so
somehow close to it.

For d internet use, the database is out of the reach of everone apart from me.


Users have a choice use it on the internet, if dy choose rather to use it on a network, then it is their
responsibility to secure dr server computer. Remember that their is licence agreement and i jave a good lawyer who prepares it.

if a person chooses to use it in desktop mode it is his responsibility to secure his app. it is now considered a personal app.

Concerning sql injection. Parameterized queries prevent that.

Apart from all this even if one discovers it . it has two level aes encrytion of the entire database and d string content.


But if u have any link on microsoft ethics dt forids pla share it wit me.