SQL View Doesn't work for comma seperated variables

Hi
I am trying to create an SQLView where i am using a variable (populationFormSOU1). The result of this variable is comma separated as :- shvnNIx,1biz5LW,BZuwzHr5,RP4CW,lYFre,n5z2VKrv,5Ut1oxMO,FJETOdBp,h7sbFDA

SELECT COUNT(*) FROM completedatasetregistration WHERE sourceid IN (populationFormSOU1=‘${populationFormSOU1}’) AND datasetid = 52 AND periodid = ‘${periodid}’;

The result of above SQLView says that the variables are invalid image

···

Seems that the query doesn’t take variables having any special character.

Any help will be appreciated.

Regards
Priyanka

This error comes in version 2.20.

image

···

On Fri, May 27, 2016 at 11:36 AM, Priyanka Bawa priyankabawa609@gmail.com wrote:

Hi
I am trying to create an SQLView where i am using a variable (populationFormSOU1). The result of this variable is comma separated as :- shvnNIx,1biz5LW,BZuwzHr5,RP4CW,lYFre,n5z2VKrv,5Ut1oxMO,FJETOdBp,h7sbFDA

SELECT COUNT(*) FROM completedatasetregistration WHERE sourceid IN (populationFormSOU1=‘${populationFormSOU1}’) AND datasetid = 52 AND periodid = ‘${periodid}’;

The result of above SQLView says that the variables are invalid
Seems that the query doesn’t take variables having any special character.

Any help will be appreciated.

Priyanka

Regards

Regards
Priyanka

Hi there.

This syntax is not supported as you can see from the documentation
The variable parameter must contain alphanumeric characters only. The variables must contain alphanumeric, dash, underscore and whitespace characters only.

This might be something which can added, but there could be an increased risk of SQL injection attacks, which these restrictions are meant to try and prevent to some extent. Maybe the developers can say more about this.

Regards,

Jason

image

···

On Fri, May 27, 2016 at 8:14 AM, Priyanka Bawa priyankabawa609@gmail.com wrote:

This error comes in version 2.20.


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

Priyanka

Regards

On Fri, May 27, 2016 at 11:36 AM, Priyanka Bawa priyankabawa609@gmail.com wrote:

Hi
I am trying to create an SQLView where i am using a variable (populationFormSOU1). The result of this variable is comma separated as :- shvnNIx,1biz5LW,BZuwzHr5,RP4CW,lYFre,n5z2VKrv,5Ut1oxMO,FJETOdBp,h7sbFDA

SELECT COUNT(*) FROM completedatasetregistration WHERE sourceid IN (populationFormSOU1=‘${populationFormSOU1}’) AND datasetid = 52 AND periodid = ‘${periodid}’;

The result of above SQLView says that the variables are invalid
Seems that the query doesn’t take variables having any special character.

Any help will be appreciated.

Priyanka

Regards

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Hi Priyanka,

I had asked a related question recently about the ability to support “IN” clauses, you can view the thread here:

https://lists.launchpad.net/dhis2-devs/msg44763.html

Lorill

image

···

On Thu, May 26, 2016 at 11:34 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi there.

This syntax is not supported as you can see from the documentation
The variable parameter must contain alphanumeric characters only. The variables must contain alphanumeric, dash, underscore and whitespace characters only.

This might be something which can added, but there could be an increased risk of SQL injection attacks, which these restrictions are meant to try and prevent to some extent. Maybe the developers can say more about this.

Regards,

Jason


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 Fri, May 27, 2016 at 8:14 AM, Priyanka Bawa priyankabawa609@gmail.com wrote:

This error comes in version 2.20.


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

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Priyanka

Regards

On Fri, May 27, 2016 at 11:36 AM, Priyanka Bawa priyankabawa609@gmail.com wrote:

Hi
I am trying to create an SQLView where i am using a variable (populationFormSOU1). The result of this variable is comma separated as :- shvnNIx,1biz5LW,BZuwzHr5,RP4CW,lYFre,n5z2VKrv,5Ut1oxMO,FJETOdBp,h7sbFDA

SELECT COUNT(*) FROM completedatasetregistration WHERE sourceid IN (populationFormSOU1=‘${populationFormSOU1}’) AND datasetid = 52 AND periodid = ‘${periodid}’;

The result of above SQLView says that the variables are invalid
Seems that the query doesn’t take variables having any special character.

Any help will be appreciated.

Priyanka

Regards