connection string Connection string encryption

[Soji 1]

We use an config file to store the database connection string. We are thinking about encrypting the connection string.

Is there any best practices to keep the connection string information safe?

[Markus Kinzler 174]

You may encrypt it with Rijndael (AES). Where are several libraries wich support it.

[David Heffernan 2345]

Although do anticipate that any hacker will be able to see the plain text when you decrypt in memory. 

[Angus Robertson 574]

Encrypting a string or password is easy, harder is how you protect the key used for the encryption. 

 

Angus

 

[Tntman 14]

You are storing information to connect to remote database fore example MySql ? It is not good to store any password related stuff in any application, have you consider making API and perform DB request over it ?

[Soji 1]

9 minutes ago, Angus Robertson said:

harder is how you protect the key used for the encryption.

Yes. I was thinking same.

[Soji 1]

6 minutes ago, Tntman said:

You are storing information to connect to remote database fore example MySql ? It is not good to store any password related stuff in any application, have you consider making API and perform DB request over it ?

It is a legacy application. Sustain mode. So looking for a quick solution.

[Vandrovnik 214]

36 minutes ago, Tntman said:

You are storing information to connect to remote database fore example MySql ? It is not good to store any password related stuff in any application, have you consider making API and perform DB request over it ?

Hmm, and this API service will have to store password for the database somewhere... And his original application will have to store password for the API service somewhere, so now he has to store two passwords 🙂

[Tntman 14]

1 hour ago, Vandrovnik said:

Hmm, and this API service will have to store password for the database somewhere... And his original application will have to store password for the API service somewhere, so now he has to store two passwords 🙂

When he submit credentials ( for example email and user password) over post request to api server will validate credentials and if true server will return JSON token, that token will be stored on users phone. you can set token expiration time for example 30 min and that token will be valid only for that particular user for next 30 minutes... After retriving token you will be submitting that token for every future request and server will walidate it.. SO basically he is talking to web server, web server validate data and talks to DB. It is way more secure..  Password for DB is stored on web server and code logic for talking to DB is on web server so users dont know DB password or logic how web server communicated to DB

 

Next security level that he could add is to ( if web app - web server  and DB are hosted on the same machine ) disable remote login, or if they are split to two different servers he can just allow his IP web server to talk and retrieve data from DB.. It is almost never good practice to talk directly to DB from almost any app ..

Sry if i was offtopic

Edited April 1, 2020 by Tntman

[David Heffernan 2345]

56 minutes ago, Soji said:

It is a legacy application. Sustain mode. So looking for a quick solution.

So you don't mind if the security is easily circumvented? 

[Soji 1]

Just now, David Heffernan said:

So you don't mind if the security is easily circumvented? 

No.

[David Heffernan 2345]

3 hours ago, Soji said:

No.

Seems like it's not really worthwhile then.... 

[Darian Miller 361]

6 hours ago, David Heffernan said:

Although do anticipate that any hacker will be able to see the plain text when you decrypt in memory. 

If hackers are in your memory space, the game is over anyway.

 

If they are using plaintext now, the focus should be on encrypting the data at rest and ensuring it's protected in transit.  

 

 

Some discussion about Delphi specific solutions: https://stackoverflow.com/questions/8621441/what-protection-scheme-for-my-passwords

 

For securing strings in memory: https://medium.com/@svanas/creating-a-securestring-type-for-delphi-part-1-e7e78ed1807c

 

Edited April 1, 2020 by Darian Miller

[David Heffernan 2345]

If the hacker gets the program, and there is a hard coded key in the program, then it's trivial to hack. 

[Tntman 14]

1 hour ago, Darian Miller said:

If hackers are in your memory space, the game is over anyway.

 

If they are using plaintext now, the focus should be on encrypting the data at rest and ensuring it's protected in transit.  

 

 

Some discussion about Delphi specific solutions: https://stackoverflow.com/questions/8621441/what-protection-scheme-for-my-passwords

 

For securing strings in memory: https://medium.com/@svanas/creating-a-securestring-type-for-delphi-part-1-e7e78ed1807c

 

The one and only way to do this is how i described .. what i told you is best practice and how it is done, there is no other way 

 

"We use an config file to store the database connection string".  <- this is bad , security risk, it is unacceptable 

 

"We are thinking about encrypting the connection string". <- this can be a "solution" but u will never be sure that you  are safe

Is there any best practices to keep the connection string information safe?

 

People can give you ideas and solutions how to encrypt string but you will still not be safe 100%..

 

From what you told us about your application i can see that it is not that big and that u already have hosting for database. That said u can just put some php code there and make api .. U can even make api in delphi 

 

Edited April 1, 2020 by Tntman

[Angus Robertson 574]

Quote

The one and only way to do this is how i described .. what i told you is best practice and how it is done, there is no other way 

So how do you suggest the password is entered for a background service application on a hosted server?   Using a token from an authentication server is fine, but how do you get it?

 

Angus

[Tntman 14]

3 hours ago, Angus Robertson said:

So how do you suggest the password is entered for a background service application on a hosted server?   Using a token from an authentication server is fine, but how do you get it?

 

Angus

If i understood right you have mobile application with service that is working in the background. That service should perform some request/response to a database?

Here in this design you are missing crucial part and that is web application that will be hosted in the middle, web application code will be on remote server and nobody would see that code ( thats a plus, they basically cant crack it they can only imagine how that application is written although there are some other security concerns ). Your database credentials will be only in web application and only web application will be talking DIRECTLY to a DB. That is why u will be making a public interface that will be available to end user and that is called API ( APplication programming interface ).

 

your API will be public and anybody could guess you api route for example ( www.mysite.com/api/customers )

One common way of protecting routes is to restrict them, example:

 

www.mysite.com/api/customers <- this route is PUBLIC [ GET ] route. Anyone who visit this link will get list of all customers in your database in JSON format for example.

www.mysite.com/api/otherRoute <- this route is PUBLIC [ GET ] route but this route will have middleware in between request and response. Middleware will check if PERSON who is visiting this route is authenticated, it is authenticated it will return data. Anyone who visit this link ( if is authenticated - have valid token ) will get list of all customers in your database in JSON format for example.

 

You can basically put any logic in middleware, so for example if user is authenticated we will check if user role is ADMIN, if is we will return list of some stuff that only ADMIN can see. You got the point...

 

Here is brief example how it should actually work behind the scenes. User visit some api route or application itself visits it ( for example www.mysite.com/api/exampleRoute ). Webapplication is checking request, it is first checking if it is POST or GET request, it is checking also for Request parameters that are passed with request and all other stuff. If validation, middleware and all other stuff pass webapplication will connect to a DB and retrieve requested data and return to end user in JSON or some other format ( usually its json ). If validation fails webapplication will not talk to DB and it will return error response for example. 

 

Note* Web application could be written in basically any language ( PHP, node js , delphi , python , c....etc... ) I personally use PHP, Laravel ( PHP's Framework ) node and i know little bit Java/Spring...

 

*************

 JSON web tokens ( JWT ).. JWT's are lightweight implementation or u could use Oauth or Oauth2 implementation but those are more complicated and heavier.. For any of this implementations you have REST debugger in Rad Studio to help you and give you nice GUI interface instead of writing code on ur own.. 

 

NOTE* If you dont know php,nodejs,python and you dont know how to write api's and stuff i mentioned in this post you could use public API's. There is a big number of publicly avialable api's , crypto apis,weather apis or even twitch api. I used twitch api for my desktop app that tracked list of online streamers and some other stuff..

 

https://jwt.io/introduction/     <- here u can read more about JWT's i can add that JWTs are stateless so with every request you have to add them as a parameter so server can check if that token is valid,blacklisted etc... 

NOTE* when i say stateless i mean there is no actual session on web server that is attached to particular user..

 

So when webserver validate user credentials and return JSON web token you could store that web token anywhere. If we talk about websites ( I am sure that you can open local storage on your web browser and search for tokens from some particular web sites that you use ). 

 

So you could ask now " What will happen if  someone steal ADMIN token from ADMIN's browser local storage and send it with request and perform operations THAT ARE ONLY RELATED TO ADMIN " ... <- This is possible scenario , but on webserver-database end you could put field in database that will check IP, request user agent and client, if those parameters dont match with previous request it means that someone have stolen token and trying to send request with ADMIN token from different device/browser etc so u could possibly limit it or destroy token or prompt  login page again...

 

I am probably talking too much and confusing you so i will stop ... Anyway ur question was where do I suggest to save password for a background service? So u are not saving users password, you just prompt login screen so user enters his username and password and perform [ POST ] request to some of your API routes ( for example www.mysite.com/api/login ), if everything is valid you will get JSON web token from server. You save that token. Tokens could have expiration date, that expiration date is set where JSON web token is created. JSON web token that will be sent to user is created in web application.Web application and it's code is running/stored on remote server.

So everytime when background task perform some request it will grab Token and send with request. web app validate that and give proper response. If token is expired web server will give a "expired" response to your task. So your task is going to receive response , if response is " expired " it will give notification to user to log in again so he could get new token or something like that.

 

If there is no need for particular data to be accessed from users that are authenticated  you can just make a public route i explained that in the top of the post "www.mysite.com/api/customers <- this route is PUBLIC [ GET ] route. Anyone who visit this link will get list of all customers in your database in JSON format for example."

 

 

 

You wrote post here saying " Encrypting a string or password is easy, harder is how you protect the key used for the encryption.  "

In scenario that i tried to explain "key" used for encryption is on remote server, and nobody knows about it. SO that key will check against token that is sent via request.. 

 

Also search for terms "Personal access token" it is used in almost any app

 

Edited April 1, 2020 by Tntman

[Vandrovnik 214]

11 hours ago, Tntman said:

If i understood right you have mobile application with service that is working in the background...

Soji wrote "legacy application", I think it is not a mobile app, but a desktop one, which connects directly to a database.

[Soji 1]

50 minutes ago, Vandrovnik said:

Soji wrote "legacy application", I think it is not a mobile app, but a desktop one, which connects directly to a database.

It is a desktop application.  

[Tntman 14]

It does not matter. It's the same idea for mobile, desktop, watch or website... Even if we write apps only for ourselves we should use this approach. I have my own website and few apps and i still use this approach that i wrote... 

 

 

[Vandrovnik 214]

I think till now they store database password in plain text in a config file and now they want something better, so that their users cannot read password from it. If they store password xored with a secret key, it would help against "normal" users. If they do not want to store that secret key in .exe, they can generate it on the fly - for example, using a random number generator set to a specific seed and obtaining generated values. It will not be hacker-proof, but it will be curious-user-proof, which is probably good enough for a legacy application.

Edited April 2, 2020 by Vandrovnik

[Anders Melander 1783]

It sounds like you really just need protection against casual discovery of the information. In that case just obfuscate it. E.g. with a simple ROT13 or even base64 encode.

 

If someone already has access to the physical machine, and is willing to do the work required, the battle is lost any way.  There are a million ways to circumvent any local encryption scheme so forget about that.

[Edwin Yip 154]

1 hour ago, Anders Melander said:

It sounds like you really just need protection against casual discovery of the information. In that case just obfuscate it. E.g. with a simple ROT13 or even base64 encode.

 

If someone already has access to the physical machine, and is willing to do the work required, the battle is lost any way.  There are a million ways to circumvent any local encryption scheme so forget about that.

I think you nailed it, the others think either bullet-prove encryption or do nothing.

And here is a ROT13 algorithm by Andreas Rejbrand: https://stackoverflow.com/a/6800389/133516