ComputerCraft Database Systems

So this is random but I am working on it now, for my desktop environment but I am also going to make it an API. I wanted a lot more database, http and online based controls, so I am in the middle of making a CCSystems code for PHP and a Lua API/Documentation for users that want to do stuff that uses these tables, I have the hosting and the tables are created. Here are the following I am making:

+ Users/Accounts System:
————————
Let's start with this one. This is an online table that will store, username, password, email, etc. The password is encrypted of course. This means using the API (I am building), users can login and send emails to their users pretty easily. The system will have password recoverys using the email, login and information editing. This is also the main usage for ALL other systems, so if you are logged in to this, then you have an account on everything, so in a way it is standardizing this meaning we don't have to have multiple users. The encryption will be a SHA256 Hash of your password, as I don't want to have any reverse program that can get the password. So the API will have encryption methods of storing your password, although this is down to whoever takes advantage of the API and uses it to set up the program.

+ Currency/Money System:
————————
This is a type of account that will be stored as a number. The money system will allow you to pay-in money, pay-out money, set-up standing orders, etc. So it is like a small bank, of course I will be making a program that will utilize all these features.

+ Email System:
—————
This is used in Blaze. This is the same email client allowing you to send, getInbox and delete emails. All using the users table to Login etc.

+ Global Chat System:
———————
This is a long shot, but a thing that occured to me was imagine having a chat system that people can talk on, it will have chat rooms, etc. It allows users to talk to each other cross-server. There will be a web version soon!


+ App Store System:
——————-
This will be used to store programs that users make and then allow us to download them and rate them. This will all be program based, but we will have a webpage that has all the stats on it.



INFO/NOTES (PLEASE READ):
I would advise to going through any program you didn't make and check the URL it posts data to which should be mine: http://dannysmc.com/files/php/ccsystem.php as I don't want users stealing others passwords and stuff. We now have a set-up to prevent DDoS (Distributed Denial of Service) attacks as this has been a problem, but we use CloudFlare now.

Passwords are automatically hashed with the SHA256 algorithm before in the API, so all you need to do is send up the data. Example: moneysystem(username, password) of course these aren't encrypted, they are encrypted when they get put into the http.post() function.


Any ideas on what to add would be great as I want it to be used by as many people as I can!

If you wish to have a table for your own use just PM me as I am open to giving you a table, all you need to do is write the PHP script, I will add in extras that will block MYSQL Injection, and stuff then I shall upload it and it will be yours to use, of course if you want I can code a system for you, just give me a PM and I would be happy to help!

I like the idea of a generalised databse for users to login to.

One major thing is though, what you already mentioned, SQL injecton. You really need to be safe with those, so please use prepared statements on so-called 'variable queries.' With that I mean something like the following:

<?php
	if( isset($_POST['username']) &amp;&amp; isset($_POST['password']) ) {
		// the sent password should be hashed, and is hashed again on the php side
		
		$db = ;//database connection, mysqli 
		$result = $db->query("SELECT password FROM user WHERE username='".$_POST['username']."'");
		if($result->num_rows == 1) {
			echo ( mysqli_fetch_row($result)['password'] === hash("sha512", $_POST['password']) );
			return;
		}
		echo false;
	}
?>

Should be more like:

<!--?php
	if( isset($_POST['username']) &amp;&amp; isset($_POST['password']) ) {
		// the sent password should be hashed, and is hashed again on the php side
		// the hashed password should be checked before all the hashing that it does not contain illegal characters which would cause a injection, such as ; and --
		
		$db = ;//database connection, mysqli 
		
		$prep = $db->prepare("SELECT password FROM user WHERE username=?")
		$prep->bind_param("s", $_POST['username']);
		$result = $prep->execute();
		$prep->close();
		
		if($result->num_rows == 1) {
			echo ( mysqli_fetch_row($result)['password'] === hash("sha512", $_POST['password']) );
			return;
		}
		echo false;
	}
?>

Now, my PHP is not the best, but you should get what Im getting on. First sort out any possible SQL injections and then add features!

I already have this set up as I have made multiple programs running of databases, but thanks for the advice! :D/>

DannySMc, on 22 December 2014 - 02:29 PM said:

I already have this set up as I have made multiple programs running of databases, but thanks for the advice! :D/>

Using MySQL/i is not regarded as /very/ secure, I recommend using PDO.

That was common knowledge (well, no, sadly) a year ago, however don't know about now because I switched to using Node.js for almost everything, especially APIs.

RainbowDashDC, on 26 December 2014 - 07:09 AM said:

Using MySQL/i is not regarded as /very/ secure, I recommend using PDO.

That was common knowledge (well, no, sadly) a year ago, however don't know about now because I switched to using Node.js for almost everything, especially APIs.

Node.js/Django/Rails/Asp.Net MVC whilst much more preferable to PHP are almost impossible on shared hosting. So sadly us cheap people are stuck on PHP.

However you really should use PDO, prepared statements are much neater than the MySQL(i) equivalent.