SQL support

Introduction

This extension supports some libraries offering access to SQL databases. The SQL frameworks analysis is based on the evaluation of the first argument of the “query()” and “execute()” method calls. The first argument is evaluated and if it corresponds to an SQL query, a CAST_TS_Query object is created. In the case where the first argument does not correspond to a SQL query, we evaluate the second argument if it exists. Text only and parameterized SQL queries are supported. This heuristic allows us to support a large number of SQL database frameworks.

The following libraries are supported:

PostgreSQL
MySQL
MySQL2
Microsoft SQL Server (mssql)
Microsoft SQL Server (node-sqlserver)
Oracle Server
SQLite3
Tedious

PostgreSQL “pg” framework

Take the following code:

var pg = require("pg");
var conString = "pg://operator:CastAIP@localhost:2280/postgres";
var client = null;

function getTables(schema, cbTables) {
    "use strict";
    client = new pg.Client(conString);
    client.connect();
    var queryTables = client.query("SELECT table_name FROM information_schema.tables WHERE table_schema='" + 
                      schema + "' ORDER BY    table_name");

    queryTables.on("row", function (row, result) {
        result.addRow(row);
    });
    queryTables.on("end", function (result) {
        var tables = [];
        for (i = 0; i < result.rows.length; i += 1) {
            tables.push(result.rows[i].table_name);
        }
        client.end();
        cbTables(tables);
    });
};

In this example, a TypeScript query object is created and a callLink between the getTables method and that query is added. The SQL Analyzer can then link that query with the corresponding table if the table exists. In the present case, this extension creates a useSelect link to the table ’tables':

MySQL/MySQL2

Take the following code:

var connection = require('my_connection');
var router = express.Router();
var connection = require('my_connection');

router.get('/', function(req, res, next) {
    function getMostUsedApps() {
        var getmostusedapps = "SELECT a.created_by,a.name,a.category_id,t.app_id as id,COUNT(t.app_id) 
                               AS  total,a.serviceUrl,a.contractId," + "c.name as catname,p.company_name 
                               FROM track_ussd t "+ "RIGHT JOIN apps a ON t.app_id = a.id INNER JOIN categories c" + 
                               " ON a.category_id = c.id INNER JOIN profiles p ON a.created_by = p.user_id WHERE 
                               t.msisdn = '"+msisdn+"' AND a.status = 'ACTIVATE'" + "GROUP BY t.app_id ORDER BY total DESC LIMIT 3";
        
        connection.query(getmostusedapps, function(err_getmostusedapps, rows_getmostusedapps, fields_getmostusedapps) {
            render(rows_getmostusedapps);
        });
    };
});

In this example, a TypeScript query object is created and a callLink between the getMostUsedApps method and that query is added. The SQL Analyzer can then link that query with the corresponding table if the table exists. In the present case, this extension creates a useSelect link to the tables ’track_ussd’, ‘categories’, ‘apps’:

Microsoft SQL Server

Take the following code:

var sql = require('mssql');

app.delete('/rest/todos/:todo_id', function doDelete(req, res) {
    'use strict';
    var id = req.params.todo_id,
        query = 'DELETE FROM TODO WHERE ID=\'' + id + '\'';
    new sql.Request().query(query, function (error) {
        if (error) {
            console.log('delete', error);
            res.status(404).send('Error when clearing completed TODOs.');
            return;
        }
        res.end();
    });
});

In this example, a TypeScript query object is created and a callLink between the anonymous function and that query is added. The sql analyzer can then link that query with the corresponding table if the table exists. In the present case, this extension creates a useDelete link to the table ‘TODO’:

Microsoft SQL Server (node-sqlserver)

Take the following code:

var sql = require('node-sqlserver');

var connStr = "Driver={SQL Server Native Client 11.0};Server=myySqlDb,1433;Database=DB;UID=Henry;PWD=cat;";
var query = "SELECT * FROM GAData WHERE TestID = 17";

sql.open(connStr, function doSelect(err,conn){
    if(err){
        return console.error("Could not connect to sql: ", err);
    }

    conn.queryRaw("SELECT TOP 10 FirstName, LastName FROM authors", function (err, results) {
        if (err) {
            console.log("Error running query!");
            return;
        }
        for (var i = 0; i < results.rows.length; i++) {
            console.log("FirstName: " + results.rows[i][0] + " LastName: " + results.rows[i][1]);
        }
    });
});

Oracle Server

Take the following code:

var oracledb = require('oracledb');
 
oracledb.getConnection(
  {
    user          : "hr",
    password      : "welcome",
    connectString : "localhost/XE"
  },
  function doSelect(err, connection)
  {
    if (err) { console.error(err); return; }
    connection.execute(
      "SELECT department_id, department_name "
    + "FROM titles "
    + "WHERE department_id < 70 "
    + "ORDER BY department_id",
      function(err, result)
      {
        if (err) { console.error(err); return; }
        console.log(result.rows);
      });
  });
};

In this example, a TypeScript query object is created and a callLink between the anonymous function and that query is added. The SQL Analyzer can then link that query with the corresponding table if the table exists. In the present case, this extension creates a useSelect link to the table ’titles’:

SQLite3

Take the following code:

const sqlite3 = require('sqlite3');
const db = new sqlite3.Database("data.sqlite");

db.serialize(() => {
  db.run("CREATE TABLE lorem (info TEXT)");

  const stmt = db.prepare("INSERT INTO lorem VALUES (?)");
  for (let i = 0; i < 10; i++) {
    stmt.run("Ipsum " + i);
  }
  stmt.finalize();

  db.each("SELECT rowid AS id, info FROM lorem");
});

In this example, three TypeScript query objects are created and three callLink between the anonymous function and these query are added. The SQL Analyzer can then link that query with the corresponding table if the table exists:

Tedious

Take the following code:

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;

const connection = new Connection(config);

connection.connect((err) => {
  const sql = 'CREATE TABLE ${table} ([c1] [int]  DEFAULT 58, [c2] [varchar](30))';
  const request = new Request(sql, (err) => {
    if (err) {
      throw err;
    }
  });
  connection.execSql(request);
});