Node.js and SQL to check a database for results

Question

I have this challenge that I had done with PHP, but wanted to see how it be done via node.js and SQL.

I am a beginner with this, and not sure at all if what I even did is legal, but am wanting to learn to do it in node; the challenge is this:

We would like a function that takes in the UserAgent of a browser as a string, and compares it to a list of approved browsers and versions

The database structure looks this:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| bigIntPKID | varchar(50) BrowserName | varchar(50) VersionNumber | bitGreaterVersions |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|         1       |           Internet Explorer        |                       9                     |             False          |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|         1       |           Internet Explorer        |                       10                   |             False          |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|         1       |           Chrome                      |                       26                   |             True           |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|         1       |           Firefox                        |                       22                   |             True           |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
bitGreaterVersions are any version greater than the version number is compatible.

The data table expressed in human readable format would be “Our app is compatible with IE9,10, Chrome 26+ and Firefox 22+”

Create a function that will take a user agent as a string, and return true or false if it is compatible based on the above criteria.

And then here is the node and SQL code (I hope)...

var express = require("express");
var mysql = require("mysql");

var connection = mysql.createConnection({
    host: 'localhost',
    username: 'username',
    password: 'password',
    dbname: 'browsers'
});

var app = express();

// if there is an error connecting to the database
connection.connect(function(error) {
    if(error) {
        console.log('Cannot establish connection to database...');
        return;
    }
    console.log('Database connection established...');
});

// function to check browser compatibility
function getUserAgent(userAgent, versionNumber) {

    var isBrowserCompatible;

    // access the database
    app.get("/path-to-database", function(req, res) {
        // query for the required results
        connection.query('SELECT BrowserName, VersionNumber, bitGreaterVersions FROM userAgentTable', function(error, rows, fields) {
        connection.end();

            if(error) {
                console.log('Cannot find the results...');
            }

            isBrowserCompatible = false;

            // loop over the returned table values
            for(var i = 0; i < rows.length; i++) {
                // if browser name and version number are equal to userAgent and versionNumber
                if(rows[i].BrowserName == userAgent && rows[i].VersionNumber == versionNumber) {
                    // the browser is compatible
                    isBrowserCompatible = true;
                    break;
                  // if browser name is equal to userAgent but the version number is less than versionNumber
                } else if(rows[i].BrowserName == userAgent && rows[i].VersionNumber < versionNumber) {
                    if(rows[i].bitGreaterVersions == 'False') {
                        // the browser is not compatible
                        break;
                    } else {
                        // otherwise the browser is compatible
                        isBrowserCompatible = true;
                        break;
                    }
                }
            }
            return isBrowserCompatible;
        });
    });

    return isBrowserCompatible;

}

Hopefully someone can point me in the right direction. I'm mainly looking to see if the actual function can be written legally in the way that I have it, and if not, how to go about it, logic and all. Be gentle...

Answer 1

You can use the ua-parser-js package to parse the User Agent string.

var parser = require("ua-parser-js");
// somewhere within the server request-response cycle
// you get user-agent header 
var ua = parser(req.headers["user-agent"]);
// ua.getBrowser() returns an object with the browser name and version
var details = ua.getBrowser();
// e.g. returns { name: "Chrome", version: "52.0.2743.116" }
// details.version.split(".") will return an array of strings
// ["52", "0", "2743", "116"] you are only interest in the [0] element
var browser = details.name;
var version = parseInt(details.version.split(".")[0]); // 52

Armed with the browser and version data you can query the database.

// var mysql = require('mysql');
// etc. etc.

connection.query("SELECT * FROM BrowserTable WHERE BrowserName = ?", [browser],
function(error, rows) {
  // handle error
  if (error) {
    throw error;
  }
  // process the results returned
  var isCompatible = false;
  for (var i = 0; i < rows[0].length; i++) {
    var dbVersion = parseInt(rows[0][i].VersionNumber);
    if ( ((rows[0][i].bitGreaterVersions == 'True') && (dbVersion < version))
    || (dbVersion == version) ) {
      isCompatible = true;
      break;
    }
  }
  return isCompatible;
});