Issue with date varibales for Sql View of type allows for variables

Support - Assistance technique
1

Hi,

I have created a sample sql view which has 2 variable parameters namely startDate and endDate. My sql view looks like:

SELECT dv.* from datavalue dv inner join period p on dv.periodid = p.periodid where p.periodtypeid = 8 and p.startdate >= ‘${startDate}’ and p.enddate <=‘${endDate}’ limit 500;

I am trying to pass these 2 date values from api url, but getting below error message:

URI:

https://apps.dhis2.org/demo/api/sqlViews/Eod3B6ET3dw/data.json?var=startDate:2015-01-01&var=endDate:2015-03-31

Response:

{

  • httpStatus: “Conflict”,

  • httpStatusCode: 409,

  • status: “ERROR”,

  • message: “SQL query contains variables which were not supplied in request: [endDate, startDate]”

}

If I place these date values inside sqlview then I am able to get the result.

Can you please help me to find where I am doing mistake. Thanks.

···

Regards,
Bharath Kumar. Ch

2

The dates should be like this: 20150101 and 20151231. Actually, the manual is confusing on this point, as it starts with dashes:

https://www.dhis2.org/doc/snapshot/en/developer/html/ch01s04.html

···

On Tue, Sep 15, 2015 at 8:26 AM, Bharath chbharathk@gmail.com wrote:

Hi,

I have created a sample sql view which has 2 variable parameters namely startDate and endDate. My sql view looks like:

SELECT dv.* from datavalue dv inner join period p on dv.periodid = p.periodid where p.periodtypeid = 8 and p.startdate >= ‘${startDate}’ and p.enddate <=‘${endDate}’ limit 500;

I am trying to pass these 2 date values from api url, but getting below error message:

URI:

https://apps.dhis2.org/demo/api/sqlViews/Eod3B6ET3dw/data.json?var=startDate:2015-01-01&var=endDate:2015-03-31

Response:

{

  • httpStatus: “Conflict”,
  • httpStatusCode: 409,
  • status: “ERROR”,
  • message: “SQL query contains variables which were not supplied in request: [endDate, startDate]”

}

If I place these date values inside sqlview then I am able to get the result.

Can you please help me to find where I am doing mistake. Thanks.

–

Regards,
Bharath Kumar. Ch

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–

Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

3

Thanks Knut. without dashes I am able to get the data.

···

On Tue, Sep 15, 2015 at 1:18 PM, Knut Staring knutst@gmail.com wrote:

The dates should be like this: 20150101 and 20151231. Actually, the manual is confusing on this point, as it starts with dashes:

https://www.dhis2.org/doc/snapshot/en/developer/html/ch01s04.html

–

On Tue, Sep 15, 2015 at 8:26 AM, Bharath chbharathk@gmail.com wrote:

Hi,

I have created a sample sql view which has 2 variable parameters namely startDate and endDate. My sql view looks like:

SELECT dv.* from datavalue dv inner join period p on dv.periodid = p.periodid where p.periodtypeid = 8 and p.startdate >= ‘${startDate}’ and p.enddate <=‘${endDate}’ limit 500;

I am trying to pass these 2 date values from api url, but getting below error message:

URI:

https://apps.dhis2.org/demo/api/sqlViews/Eod3B6ET3dw/data.json?var=startDate:2015-01-01&var=endDate:2015-03-31

Response:

{

  • httpStatus: “Conflict”,
  • httpStatusCode: 409,
  • status: “ERROR”,
  • message: “SQL query contains variables which were not supplied in request: [endDate, startDate]”

}

If I place these date values inside sqlview then I am able to get the result.

Can you please help me to find where I am doing mistake. Thanks.

–

Regards,
Bharath Kumar. Ch

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Regards,
Bharath Kumar. Ch

4

Hi Bharath,

sorry I know this is a bit unclear. For security purposes we have strict checks on the URL variables to prevent malicious values from being executed as SQL. We are currently only allowing alphanumeric values so it stopped your dates since they have dashes inside. I have made two changes and backported them to 2.20 now:

  • For variable values we now allow characters, numbers, dash, underscore and space.

  • I have implemented better feedback so that the API will tell you which variables are invalid in the response.

  • I have also updated the docs to reflect this.

Please try again with latest 2.20.

regards,

Lars

···

On Tue, Sep 15, 2015 at 11:10 AM, Bharath chbharathk@gmail.com wrote:

Thanks Knut. without dashes I am able to get the data.

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–

On Tue, Sep 15, 2015 at 1:18 PM, Knut Staring knutst@gmail.com wrote:

The dates should be like this: 20150101 and 20151231. Actually, the manual is confusing on this point, as it starts with dashes:

https://www.dhis2.org/doc/snapshot/en/developer/html/ch01s04.html

–

Regards,
Bharath Kumar. Ch

On Tue, Sep 15, 2015 at 8:26 AM, Bharath chbharathk@gmail.com wrote:

Hi,

I have created a sample sql view which has 2 variable parameters namely startDate and endDate. My sql view looks like:

SELECT dv.* from datavalue dv inner join period p on dv.periodid = p.periodid where p.periodtypeid = 8 and p.startdate >= ‘${startDate}’ and p.enddate <=‘${endDate}’ limit 500;

I am trying to pass these 2 date values from api url, but getting below error message:

URI:

https://apps.dhis2.org/demo/api/sqlViews/Eod3B6ET3dw/data.json?var=startDate:2015-01-01&var=endDate:2015-03-31

Response:

{

  • httpStatus: “Conflict”,
  • httpStatusCode: 409,
  • status: “ERROR”,
  • message: “SQL query contains variables which were not supplied in request: [endDate, startDate]”

}

If I place these date values inside sqlview then I am able to get the result.

Can you please help me to find where I am doing mistake. Thanks.

–

Regards,
Bharath Kumar. Ch

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org

5

Thanks Lars, I am updating my local 2.20 copy, will test it.

···

On Tue, Sep 15, 2015 at 3:15 PM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Bharath,

sorry I know this is a bit unclear. For security purposes we have strict checks on the URL variables to prevent malicious values from being executed as SQL. We are currently only allowing alphanumeric values so it stopped your dates since they have dashes inside. I have made two changes and backported them to 2.20 now:

  • For variable values we now allow characters, numbers, dash, underscore and space.
  • I have implemented better feedback so that the API will tell you which variables are invalid in the response.
  • I have also updated the docs to reflect this.

Please try again with latest 2.20.

regards,

Lars

–

On Tue, Sep 15, 2015 at 11:10 AM, Bharath chbharathk@gmail.com wrote:

Thanks Knut. without dashes I am able to get the data.

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org

On Tue, Sep 15, 2015 at 1:18 PM, Knut Staring knutst@gmail.com wrote:

The dates should be like this: 20150101 and 20151231. Actually, the manual is confusing on this point, as it starts with dashes:

https://www.dhis2.org/doc/snapshot/en/developer/html/ch01s04.html

–

Regards,
Bharath Kumar. Ch

On Tue, Sep 15, 2015 at 8:26 AM, Bharath chbharathk@gmail.com wrote:

Hi,

I have created a sample sql view which has 2 variable parameters namely startDate and endDate. My sql view looks like:

SELECT dv.* from datavalue dv inner join period p on dv.periodid = p.periodid where p.periodtypeid = 8 and p.startdate >= ‘${startDate}’ and p.enddate <=‘${endDate}’ limit 500;

I am trying to pass these 2 date values from api url, but getting below error message:

URI:

https://apps.dhis2.org/demo/api/sqlViews/Eod3B6ET3dw/data.json?var=startDate:2015-01-01&var=endDate:2015-03-31

Response:

{

  • httpStatus: “Conflict”,
  • httpStatusCode: 409,
  • status: “ERROR”,
  • message: “SQL query contains variables which were not supplied in request: [endDate, startDate]”

}

If I place these date values inside sqlview then I am able to get the result.

Can you please help me to find where I am doing mistake. Thanks.

–

Regards,
Bharath Kumar. Ch

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Regards,
Bharath Kumar. Ch

6

Thanks.

···

On Tue, Sep 15, 2015 at 12:26 PM, Bharath chbharathk@gmail.com wrote:

Thanks Lars, I am updating my local 2.20 copy, will test it.

–

On Tue, Sep 15, 2015 at 3:15 PM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Bharath,

sorry I know this is a bit unclear. For security purposes we have strict checks on the URL variables to prevent malicious values from being executed as SQL. We are currently only allowing alphanumeric values so it stopped your dates since they have dashes inside. I have made two changes and backported them to 2.20 now:

  • For variable values we now allow characters, numbers, dash, underscore and space.
  • I have implemented better feedback so that the API will tell you which variables are invalid in the response.
  • I have also updated the docs to reflect this.

Please try again with latest 2.20.

regards,

Lars

–

Regards,
Bharath Kumar. Ch

On Tue, Sep 15, 2015 at 11:10 AM, Bharath chbharathk@gmail.com wrote:

Thanks Knut. without dashes I am able to get the data.

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org

On Tue, Sep 15, 2015 at 1:18 PM, Knut Staring knutst@gmail.com wrote:

The dates should be like this: 20150101 and 20151231. Actually, the manual is confusing on this point, as it starts with dashes:

https://www.dhis2.org/doc/snapshot/en/developer/html/ch01s04.html

–

Regards,
Bharath Kumar. Ch

On Tue, Sep 15, 2015 at 8:26 AM, Bharath chbharathk@gmail.com wrote:

Hi,

I have created a sample sql view which has 2 variable parameters namely startDate and endDate. My sql view looks like:

SELECT dv.* from datavalue dv inner join period p on dv.periodid = p.periodid where p.periodtypeid = 8 and p.startdate >= ‘${startDate}’ and p.enddate <=‘${endDate}’ limit 500;

I am trying to pass these 2 date values from api url, but getting below error message:

URI:

https://apps.dhis2.org/demo/api/sqlViews/Eod3B6ET3dw/data.json?var=startDate:2015-01-01&var=endDate:2015-03-31

Response:

{

  • httpStatus: “Conflict”,
  • httpStatusCode: 409,
  • status: “ERROR”,
  • message: “SQL query contains variables which were not supplied in request: [endDate, startDate]”

}

If I place these date values inside sqlview then I am able to get the result.

Can you please help me to find where I am doing mistake. Thanks.

–

Regards,
Bharath Kumar. Ch

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Knut Staring

Dept. of Informatics, University of Oslo

Norway: +4791880522

Skype: knutstar

http://dhis2.org

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org