Express/Knex/Sql query with dynamic interpolation not working

Question

I have an Express/Postgres backend with the following route that returns a leaderboard based on points accumulated over the past X days. eg, when I hardcode 'points_horizon' in the query below as 7, it returns the leaderboard based on the past 7 days. However, I want to be able to specify the attribute points_horizon for each group such to adjust the number of days the leaderboard includes. However, the following interpolation does not work, returning the error: ReferenceError: points_horizon is not defined

// returns total number of acts per user over the last 'points_horizon' days
router.get('/leaderboard/:group_id', async (req, res) => {
  let results = await knex.raw('
SELECT memberships.users_id, users.username, avatar_url, COUNT(acts.users_id) 
FROM memberships 
JOIN groups ON memberships.groups_id = groups.id 
JOIN users ON memberships.users_id = users.id 
LEFT JOIN acts ON acts.users_id = users.id 
AND acts.created_at >= (CURRENT_DATE - ' + points_horizon + ') 
WHERE memberships.groups_id = ' + req.params.group_id + ' 
GROUP BY memberships.users_id, users.username, avatar_url 
ORDER BY COUNT(acts.id) DESC');
  console.log('This is the reports/leaderboard query response', results.rows)
  res.json(results.rows);
});

to add more details, here is how points_horizon gets set:

here is the leaderboard component:

<template>
  <div class="table-responsive mt-2">
    <table class="ui celled table">
      <thead>
        <tr><th colspan="4">Leaderboard for {{ currentGroup.name }}</th></tr>
        <tr>
          <th>Username</th>
          <th>Last Action</th>
          <th>Date</th>
          <th>Score</th>
        </tr>
      </thead>
      <tbody>
        <tr v-for="(leader) in leaderboard" :key="leader.users_id">
          <td>
            <h4 class="ui image header">
              <img v-if="leader.avatar_url" :src="leader.avatar_url"  class="ui mini rounded image">
              <img v-else :src="'https://robohash.org/'+ leader.username" class="ui mini rounded image"/>
              <router-link :to="`/users/${leader.users_id}`" class="content">
                {{leader.username}}
              </router-link>
            </h4>
          </td>
          <td>{{ lastUserAct.deed }}</td>
          <td></td>
          <!-- <td>{{ lastAct(leader.id).deed }}</td>
          <td>{{ moment(lastAct(leader.id).created_at).strftime("%A, %d %b %Y %l:%M %p") }}</td> -->
          <td>{{leader.count}}</td>
        </tr>
      </tbody>
    </table>
  </div>
</template>

<script>
import moment from 'moment-strftime'
import _ from 'lodash'
import ReportsService from '@/services/ReportsService'
import ActsService from '@/services/ActsService'
export default {
  name: "Leaderboard",
  data() {
    return {
      lastUserAct: {}
    }
  },
  computed: {
    leaderboard () {
      return this.$store.getters.leaderboard;
    },
    currentGroup () {
      return this.$store.getters.currentGroup;
    }
    // ,
    // lastAct (userId) {
    //   return _.orderBy(this.actsByUser(userId), 'created_at')[0];
    // }
  },
  mounted () {
    this.getLeaderboard();
  },
  methods: {
    getLeaderboard: async function () {
      console.log('in LeaderBoard, this is currentGroup: ', this.$store.getters.currentGroup.name)
      this.$store.dispatch("updateLeaderboard", this.currentGroup);
    },
    moment: function (datetime) {
      return moment(datetime);
    }
    ,
    async lastActByUser (leader_id) {
      console.log('in Leaderboard, getting last act for user')
      const response = await ActsService.fetchLastActByUser ({
        userId: leader_id
      });
      this.lastUserAct = response.data
      console.log('in Leaderboard, lastAct response: ', response.data)
    }
  }
};
</script> 

here is store.js:

import Vue from 'vue'
import Vuex from 'vuex'
import axios from 'axios'
import PostsService from './services/PostsService'
import ReportsService from './services/ReportsService'
...

Vue.use(Vuex)

export default new Vuex.Store({
  state: {
    ...
    leaderboard: [],
    currentGroup: {},
    lastAct: '',
    ...
  },
  mutations: {
    ...
    setLeaderboard(state, leaderboard) {
      state.leaderboard = leaderboard
    },
    setCurrentGroup(state, group) {
      state.currentGroup = group
    },...
  },
  actions: {
    ...
    getUserGroups({ commit }) {
      GroupsService.getGroups()
        .then(resp => {
          console.log('in store getUserGroups, this is usergroups: ', resp);
            commit('setCurrentGroup', resp.data[0]);
        });
    },
    updateLeaderboard({ commit }, group) {
      ReportsService.getLeaderboard(group.id)
        .then(resp => {
          commit('setLeaderboard', resp);
        });
    },...
  },
  getters: {
    ...
    leaderboard: state => {
      return state.leaderboard;
    },
    currentGroup: state => {
      return state.currentGroup;
    },

  }
})

here is ReportsService which calls the api:

import axios from 'axios'

export default {
  async getLeaderboard (group_id) {
    let response = await axios.get('reports/leaderboard/' + group_id) 
    console.log('In ReportsService, leaderboard response: ', response.data.id);
    if (response.status === 200) {
      return response.data;
    }
  }
}

I guess the question is how to pass the group to the express rout s.t. I can use it in the query.

a few changes, but still not able to retrieve group attributes:

I adjusted the relevant action in store.js to this:

updateLeaderboard({ commit }, group) {
  ReportsService.getLeaderboard(group)
    .then(resp => {
      commit('setLeaderboard', resp);
    });
},

changed ReportsService to:

import axios from 'axios'

export default {
  async getLeaderboard (group) {
    let response = await axios.get('reports/leaderboard/' + group.id, {
      params: {
        group: group
      }
    }) 
    console.log('In ReportsService, leaderboard response: ', response.data.id);
    if (response.status === 200) {
      return response.data;
    }
  }
}

and in server side route:

// returns total number of acts per user over the last 'points_horizon' days
router.get('/leaderboard/:group_id', async (req, res) => {
  console.log('this is req group object: ', req.query.group)
  let results = await knex.raw('SELECT memberships.users_id, users.username, avatar_url, COUNT(acts.users_id) FROM memberships JOIN groups ON memberships.groups_id = groups.id JOIN users ON memberships.users_id = users.id LEFT JOIN acts ON acts.users_id = users.id AND acts.created_at >= (CURRENT_DATE - 13) WHERE memberships.groups_id = ' + req.params.group_id + ' GROUP BY memberships.users_id, users.username, avatar_url ORDER BY COUNT(acts.id) DESC');
  console.log('This is the reports/leaderboard query response', results.rows)
  res.json(results.rows);
});

in console.log, req.query.group returns:

this is req group object:  {"id":2,"name":"Tuesday Group","description":"We meet every other Tuesday in person!","created_at":null,"updated_at":null,"owners_id":null,"max_members":10,"private_group":false,"address":null,"latitude":null,"longitude":null,"points_horizon":14}

However, req.query.group.id and req.query.group["id"] returns undefined.

req.query.group.constructor.name returns String

last update: JSON.parse(req.query.group).points_horizon returns the correct value. However, I don't understand why req.query.group does not return json, and I don't know if this is the right way to do this...

Answer 1

Since there is not enough data to give any better answer, the problem here is that your variable points_horizon does not exist (as comment pointed out).

Also the way you are using knex is vulnerable to SQL injection attacks. You should be using ? parameter binding when you are passing literal values to the queries so that the database driver can handle escaping your variables. More info about how knex handles protection from SQL injection attacks is found here:

Does Knex.js prevent sql injection?