How to create a Local Database System

[windowsxp37]

I'm looking to create a database system which will run locally on an old XP Laptop, but am unsure as to the best way to create this system.

I have much experience with developing web-based database systems using PHP, MYSQL and HTML, but need to find a suitable way to create a secure local system that will not require access to the internet.

 

I would ideally like to create some kind of Windows command line program, or even some sort of GUI - I was originally thinking of finding a way to link local HTML with Microsoft SQL Server, but I can't see how that would work.

If anyone knows of a way to secure data in a database system that can be edited and added to using a front-end program, any ideas would be much appreciated. Do note that there would need to be some kind of security protocol involved in accessing the database, such as inputting usernames / passwords for access (not sure the best way to store these securely, locally too)

 

Many thanks.

 

[mariushm]

Agreed. Use SQLite or MariaDB (open source MySQL) configured to accept connections on local ip only

 

 

You can hash passwords and store the hashes in database...when user enters a username and password, you calculate a hash for that password - username combo and check it against the existing database

 

You can set up a super basic apache http server on windows as a service and set the website on the local ip, at some random port - i often use 9999 but anything above 1024 is should be pretty much safe (i'd avoid 8000 and 8080 as they're often used by proxies but if you know what's running on the computer you can use them)

[wasab]

I use docker since my boss sends me mysql data dumps all the time. I would just spin up a new container, import the data, run whatever queries I need inside, export the stuffs. Delete container, rinse and repeat. 

 

To get datas, you would need a backend. php alongside nginx is old school but it works. I would also get a mysql client like datagrip.

Edited April 23, 2021 by wasab

[windowsxp37]

19 hours ago, Sakuriru said:

You can always run MySQL and Postgres locally (and connect via localhost), but that requires running a server on your machine (or in a container). Fortunately someone thought about this before and created SQLite. It acts as a regular SQL engine, but it's headless so the configuration is much easier.

Thanks for the reply.

SQLite sounds like a good idea, especially as it can run in a simple way, locally.

I assume that SQLite works in the same way as usual web SQL databases, so it will be able to integrate within HTML/PHP code - seems like this link provides a good tutorial > https://www.tutorialspoint.com/sqlite/sqlite_php.htm 

[windowsxp37]

18 hours ago, mariushm said:

Agreed. Use SQLite or MariaDB (open source MySQL) configured to accept connections on local ip only

 

 

You can hash passwords and store the hashes in database...when user enters a username and password, you calculate a hash for that password - username combo and check it against the existing database

 

You can set up a super basic apache http server on windows as a service and set the website on the local ip, at some random port - i often use 9999 but anything above 1024 is should be pretty much safe (i'd avoid 8000 and 8080 as they're often used by proxies but if you know what's running on the computer you can use them)

Yep - SQLite seems like the best plan.

Thanks for the info on hashing passwords - hopefully that will work with the login code etc., but will see how it goes.