COnnecting mango to ext. DB for Verification

zdabbas

Hello

I have a situation I need your help with.

I have several devices defined as data sources with data points defined as 2x RFID readers, a digital input and 2x digital output (relays). These are working no problem.

What I need to do is as follows:

when someone swipes the RFID card Mango needs to access an Oracle DB (using JDBC or any other tool) verify that the RFID card number is authorized to access and then close one of the relays.
I did do some reading on it and it seems to be done using the SQL data source. But how do I make sure that JDBC is installed and how do I read the data from the DB rather than only write it.

The trigger for verifying the card number would be the change of value on the RFID data point I assume.

So Basically I need Mango to verifying an incoming data point's value (the RFID card number) against a database, then performing a control action as a function of that.

I'm in a bit of a dilemma need some help I'm totally lost with this.

I really appreciate your help

phildunlap

Hi zdabbas,

I think getting this to work responsively with the SQL data source would be very tricky. There is no way to modify the select statement on the fly or force a poll conveniently. For these reasons, i would solve this with an SSH data source. To your other questions, H2 and MySQL JDBC drivers are supplied with Mango, but all that is required is that the JDBC jar be on the classpath somewhere, so you can add other JDBC drivers you may need to Mango/overrides/lib/

To use the SSH data source to perform this, I see the control as follows. Presumably the database has some kind of shell.

RFID reader supplies value into a data point,
Point link sets SSH point to the value of the card's key, SSH point is defined like...
queryable: true, query command: if [ ! -z $keyAuthorized ]; then echo true; else echo false; fi;
settable: true, set command: keyAuthorized=$(mysql --user=user --password=password db <<< "select key from authorizedKeys where key='VALUE'" | grep 'VALUE');
Point link the SSH point to the relay control point, toggling it when true.

You may wish to create a user specifically for the SSH data source, such that it doesn't have Mango's privileges. This will mean adding an ssh key to that user's authorized ssh keys. If you wish to conceal your database user and password from being plaintext in a Mango export you could move that logic into a "validateKey" script you invoke, i.e.

keyAuthorized=$(/path/to/validateKey.sh 'VALUE');

Which will offer you a place to sanitize the SQL if you wish (i.e. keyProspect=$(echo $1 | grep -o -Pe "^[^ ]$") or some such). The whole idea here is that we'll set a variable if the key is true and read it in the same SSH channel. As written here by me, if the keyAuthorized variable is set to any output from the validateKey script/command, then it will return true. If nothing is output, then the point will be false.

Two extra considerations are that you probably don't need to utilize polling on the SSH data source, so you can set it's poll period very long. This datasource may be one we could consider making polling an option. The other is that perhaps you are not interested in the value 'false' being set via point link to the control point for your relay, in which case I would advise something like this for the step (3) point link:

if(source.value === false)
   throw "Unauthorized key exception";
return true;

You can set the exception/error event level for Point Links on the system settings page, in the events section.

Good luck!

zdabbas

Thank you Phil for the solution.

But I have a few queries

I need to connect to an external Oracle DB not MySQL. where do I change these statements.
where do I read from the database (SQL Query) so I can see if the RFID key is Authorized or not.
I can't see anywhere the password for SSH where is that entered.(or is it the authorized key?
do I need the JDBC Driver?

variables and Commands:-

Oracle DB server : 192.0.0.15
Command to enter SQL:: sqlplus <username>/<password>
Command for query: : select dac_door('<Card Number>','<Door Number>') from dual
<Card Number> is the RFID Number read by the BACnet unit
<Door Number> is a variable defined in Mango which changes the IP address of the BACNet unit to a Door Number e.g 172.50.0.20 = D201

The query returns a Y or N on the SSH command line., (SQLplus prompt). based on this relay mango then activates or NOT the relay on the BACnet unit, and after five seconds it returns it to normal.

I appreciate your help in advance.

phildunlap

That MySQL invocation is on the command line. So, if sqlplus in on your command path you can just replace mysql with sqlplus in those examples.

The script or call made through the SSH point will read from the database. This is why I have a here-string describing the SELECT with the <<<.

The SSH data source uses RSA authorized keys, so you would have to generate that for a user you can accept having SSH access, which is why I recommended making a low privilege user on your operating system. The data source help dialogue would be useful to you on this task.

You do not need a JDBC driver since you would be invoking the database shell, and not trying to access it from Java.

I found this thread interesting in learning some about sqlplus: http://stackoverflow.com/questions/10277983/connect-to-sqlplus-in-shell-script-and-run-a-sql-script-file

Are you also needing to get the door number back? I grepped for VALUE (which I expected to be the RFID value) such that there would be no output from the database call / script which invokes the database unless it's successful. You could grep for 'Y' or whatever fits the situation, or you can just make the script not output anything in the failure case (or output 'FAIL' and change the query command to 'echo $keyAuthorized' and handle the 'FAIL' case in the point link to the relay).

I would use an event detector and handler on the relay control point. A state handler for 'true', execution delay five seconds, set state handler that sets the relay point back to false.

zdabbas

Thank phil I will try this,

as for the door number. on the BACnet unit the IP address is the door number, but in the Database the door number is in the format D001 (Dxxx), so I would need to convert the IP to a door number to present it to the database with the RFID number.

is there an easy way of doing this?

I was thinking that I can have a MySQL database with the IP address against the door numbers and have a script that generates a variable that will give the SSH data source the door number when it communicates with the database.

or each unit will have it's name as the door number and we can take that into the SSH command is that a more viable solution, if so how can I add that to the SSH command?
what do you think?

phildunlap

I would think that could be achieved, but I'm not sure how you're acquiring the IP address of the device. Is that the address of the card reader? If you have the IP on hand, you can set it to the SSH point and let it do the database work. Or you can load the whole mapping into Mango's scripting environment.

I would try the former, but it would rely on not having the space character ' ' be valid in your RFID codes. You can set a value to the SSH point with spaces and it will be parsed as though it were entered in a terminal. So, setting your ssh point to "RFID# x.x.x.x" then $1 is your RFID in the script, and $2 is the IP address. If there are spaces, you can just wrap those two values in single quotes.

zdabbas

Let me explain what the BACnet device is:-

the BACnet device is a Beagle Bone Black with BACnet stack running on it, the card reader is a serial device connected to the BBB and the relays are on a cap on the BBB.

the BBB is connected to Mango through TCP/IP each BBB is installed on one door and each one has an IP address.

when configuring it on Mango it is configured as a Data Source using BACnet TCP/IP and the card reader, relays are data points on that data source.

when I configure the BBB on Mango I give it a name (D001) and enter the IP Address and so on.

Now the Oracle Database doesn't have the IP address of the unit. it only has the door number in this case D001. so when I want to see the authorization I present to the database the Card number and the door number (which is D001).

to achieve this I need to present to the DB the name of the unit (that is found on the name of the Data source) and the card number that was read from it.

I hope this explains things a bit more clearly,.

phildunlap

The point links solution still sounds good to me. You'll need an SSH data point for each RFID point I think. Then you can just hardcode the door number either into the pointlink or the ssh point. I would opt for the point like, and set the SSH point's value to something like "RFID# Door#" and stick with the ssh points' set commands being "key=$(/path/to/validation/script.sh VALUE)" as the string VALUE will be replace by our two arguments for the script.

Each RFID point will have a point link to an SSH point (and all SSH points can be the same, but for the sake of event handlers (if Mango needs to track state beyond just issuing a single open to the doors). Door 1's pointlink may look like,

return source.value + " D001"

You may need to check for an empty source.value and such. You can use all the same control logic from my earlier posts, involving another chain of point links from the SSH points to their relays.