We used node red to access, manage and update the database. We also used node red to create a webserver with an easy useable userinterface where we were able to manage the two tables in the database we had. In the first table we had the users with their password where we could insert users and remove users. The other table were for logging where we could see the date and time a user would log in.
The database was made with mysql. Mysql databases can be accessed in node red by downloading the mysql palette. This gives you a block in node red where you can connect the database by adding a mysqldatabase where you type the IP-address of the host, the port you use, username for the database and password if it is used, and the database’s name. To get the information from the database shown on the interface we had to create a dashboard table with a template we then added an inject input with a sql-query to keep updating the database on the interface.
<table style="width:100%"> <tr> <th>Index</th>
<th>user</th> <th>password</th> </tr>
<tr ng-repeat="x in msg.payload"> <td>{{$index}}</td>
<td align="center">{{msg.payload[$index].user}}</td>
<td align="center">{{msg.payload[$index].password}}</td>
</tr></table>
To manage the database, we made two buttons on the dashboard. One named register that would insert the text from the two text fields, user and password, into the log in table in the database.
global.set('user',msg.payload)
global.set('password',msg.payload)
msg.topic = "INSERT INTO login (user,password) VALUES ('"+global.get('user')+"', '"+global.get('password')+"')";
return msg;
The other button, delete, would delete the user which was written in the Delete user text field.
global.set('deleteUser',msg.payload)
msg.topic = "delete from login where user ='"+global.get('deleteUser')+"' ";
return msg;
We also used node red to check the database when a log in request came from the Arduino. To receive a message through WiFi from the Arduino we had to listen for udp messages that way we could receive the log in string that came from the Arduino. The string that we would receive was “username,password” which we would have to separate with a function so we would have the username in one variable and the password in another.
var data = msg.payload.split(",");
var user = data[0];
global.set('user1',user)
global.set('pass',data[1]);
That way we could check the database if the user was registered and if the password was correct.
msg.topic = "SELECT password FROM login WHERE user = '"+user+"'";
return msg;
 We would then get TRUE or FALSE which would be send back to the Arduino in a udp message.
try {
    msg.payload = msg.payload[0].password==global.get('pass');
}
catch(err) {
    msg.payload=false;
}
return msg;
 We also implemented that if the user and password was correct the user and a timestamp would be inserted into the log table, that way we could keep a logging track of who logs in.
if (msg.payload) {
    msg.topic = "INSERT INTO log (user,timestamp) VALUES ('"+global.get('user1')+"', '"+global.get('timestamp')+"')";
}
return msg;
To get the date and time would put a date/time formatter in a variable which we would keep injecting every second to get the exact time.
global.set('timestamp',msg.topic)
return msg;