Re: FYI...SQL in Set Fields MUST begin with "SELECT"

[laurent matheo]

Really? When I made a test on 7.6.04 and 8.1 the stored procedure returned a 
value, though you needed to cast it on some occasions.

https://bmccommunities.jive-mobile.com/#jive-discussion?content=%2Fapi%2Fcore%2Fv2%2Fdiscussions%2F93451
Mobilis in Mobile.

> Le 20 févr. 2014 à 21:42, Charlie Lotridge  a écrit :
> 
> **
> LJ, the doc indeed does say that there's no syntax checking, and I did chase 
> it down the rabbit hole insofar as to do a SQL log to confirm that ARS thinks 
> it's submitting the SQL to the DB.  Just to make sure, I then cut & pasted 
> that same SQL from the SQL log and into a query window and executed it - it 
> worked just as expected.
> 
> Shawn, WRT stored procedures, the doc says "A stored procedure with a Set 
> Fields action executes all its commands but does not return a value."  If 
> your stored procedure returns a result set you'd like to use in a Set Fields 
> then, again, the solution here would be to wrap the call to the stored 
> procedure in a SQL view and call that.  And, again, it's a less convenient 
> solution.
> 
> 
>> On Thu, Feb 20, 2014 at 12:30 PM, LJ LongWing  wrote:
>> **
>> the interesting thing is that what I remember of the docs state that Remedy 
>> does no syntax checking of your SQL to determine if it's accurate, it trusts 
>> you to do that.  And because of that, I must wonder if you traced the SQL 
>> all the way to the DB and determine what it's doing with thatto see if 
>> it's executing it or notnot sure what would be found if you went down 
>> that rabbit hole.
>> 
>> 
>>> On Thu, Feb 20, 2014 at 1:25 PM, Charlie Lotridge  
>>> wrote:
>>> **
>>> Interesting, thanks for this info.
>>> 
>>> I suppose this could be what's going on, but I'd think it would be more 
>>> appropriate for ARS to error if the SQL violates some rules, rather than 
>>> operate as if the query returned no results.  It's misleading.  Also, the 
>>> documentation mentions nothing about any such rules.
>>> 
>>> That Jaspersoft doc mentions that the SQL "should start with SELECT" and 
>>> "cannot have comments".  And while Remedy seems to be adhering to the first 
>>> part of this, it's not adhering to the second: I was able to insert 
>>> comments both as separate lines, and at the end of lines, and it works 
>>> correctly.
>>> 
>>> -charlie
>>> 
>>> 
 On Thu, Feb 20, 2014 at 12:12 PM, Janie Sprenger  
 wrote:
 **
 I think there are security requirements for web applications and one of 
 the requirements is to prevent SQL injection.  Not sure, but perhaps 
 Remedy is using something of this sort with the midtier.
  
 I ran into something similar with iReports and Jaspersoft when I was 
 writing an SQL query only mine happened to be with setting a variable to 
 begin with instead of a Comment.  I could run the iReport in the tool but 
 not on the JasperSoft web client.  
  
 You can read more about it here.
 http://community.jaspersoft.com/wiki/jaspersoft-security-changes-and-configuration
 
 Janie
  
> On Thu, Feb 20, 2014 at 11:59 AM, Charlie Lotridge  
> wrote:
> **
> I use quite a lot of SQL in my workflow, yet somehow never discovered 
> this one before.  It turns out that if you're using SQL to pull data back 
> for a Set Fields action, it must begin with the SELECT keyword, or it 
> won't return any results.
> 
> For example, if you have a Set Fields with this SQL:
> 
> SELECT name
> FROM arschema
> 
> it'll work fine.  But if you insert a comment before it:
> 
> -- Comment
> SELECT name
> FROM arschema
> 
> or even
> 
> /* Comment */ SELECT name
> FROM arschema
> 
> the Set Fields will operate as if "No Request Match" (i.e. it'll display 
> the No Match error, or set the target fields to NULL, depending upon how 
> you've got it configured).
> 
> What's interesting here is that the SQL in these queries is syntactically 
> correct and they're submitted to the database by ARS without any error.  
> If you submit the SQL manually (through SQL Plus or SQL Server Management 
> Studio, etc), it works correctly and returns the expected data.  
> Apparently, though, Remedy doesn't know how to deal with it if it doesn't 
> begin with the keyword SELECT.
> 
> I only just discovered this because I was attempting to use a query 
> containing a WITH clause in SQL Server to create a Common Table 
> Expression to flatten out a recursive data structure.  Using the WITH 
> clause, which MUST be first in the query (and can't be contained in a 
> subquery) is the only way to do this in a single query.
> 
> Of course, the work-around is to create a view containing the CTE, which 
> is what I ultimately had to do.  It's just a less convenient solution.
> 
> Anyway, just something interesting I just discovered.
> 
> -charlie
> _AR

Re: FYI...SQL in Set Fields MUST begin with "SELECT"

[Campbell, Paul (Paul)]

I believe it is an issue with the c libraries they use for oracle that it can't 
handle any comments, because I have used BEGIN 
procedure_name('parameter1','parameter2', $parameter3$ ); END;  before but 
comments are a no go, I also have used select function_name('$parameter$') from 
dual to get return values as well, I just put multiple values separated by 
pipes and parse it in filters

From: Action Request System discussion list(ARSList) 
[mailto:arslist@ARSLIST.ORG] On Behalf Of Joe D'Souza
Sent: Thursday, February 20, 2014 7:49 PM
To: arslist@ARSLIST.ORG
Subject: Re: FYI...SQL in Set Fields MUST begin with "SELECT"

**
It was a lot less than 400 characters on my SQL statement from what I recall. 
More like 512 or less. I recall contacting Remedy Support regarding  that and 
if I remember right they came back with its as designed. I do not recall what 
resolution I had to resort to, but I'm guessing I might have gone with a view 
form or something to complete my set field action.

Joe


From: Action Request System discussion list(ARSList) 
[mailto:arslist@ARSLIST.ORG] On Behalf Of Charlie Lotridge
Sent: Thursday, February 20, 2014 6:29 PM
To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>
Subject: Re: FYI...SQL in Set Fields MUST begin with "SELECT"

**
Yeah, things start to get funny when you surpass the varchar limit (typically 
4000 chars) and head into LOB territory.  Maybe it had something to do with 
that.

I've never used such a large query in a Set Fields, so I don't think I've ever 
seen this issue.

-charlie

On Thu, Feb 20, 2014 at 3:21 PM, Joe D'Souza 
mailto:jdso...@shyle.net>> wrote:
**
I've once hit a limitation wherein a long SQL command (which ran perfectly on 
the SQL client) didn't work within the SQL Set Fields action and returned an 
error (do not recall what error as this was many years ago). So since then I 
have tried to limit my use of SQL in Set Fields only if the number of fields 
you are returning back are few. I do not know if this limitation is now not an 
issue. If I recall right, I hit this limitation in a very early release of 7.0 
and I recall testing it on 6.3 and found that to be true on that version too.

If I recall right, the limitation was not because of the number of fields being 
returned, but the actual string length of the SQL statement.

Joe


From: Action Request System discussion list(ARSList) 
[mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] On Behalf Of Charlie 
Lotridge
Sent: Thursday, February 20, 2014 3:25 PM

To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>
Subject: Re: FYI...SQL in Set Fields MUST begin with "SELECT"

**
Interesting, thanks for this info.

I suppose this could be what's going on, but I'd think it would be more 
appropriate for ARS to error if the SQL violates some rules, rather than 
operate as if the query returned no results.  It's misleading.  Also, the 
documentation mentions nothing about any such rules.

That Jaspersoft doc mentions that the SQL "should start with SELECT" and 
"cannot have comments".  And while Remedy seems to be adhering to the first 
part of this, it's not adhering to the second: I was able to insert comments 
both as separate lines, and at the end of lines, and it works correctly.

-charlie

On Thu, Feb 20, 2014 at 12:12 PM, Janie Sprenger 
mailto:jrsrem...@gmail.com>> wrote:
**
I think there are security requirements for web applications and one of the 
requirements is to prevent SQL injection.  Not sure, but perhaps Remedy is 
using something of this sort with the midtier.

I ran into something similar with iReports and Jaspersoft when I was writing an 
SQL query only mine happened to be with setting a variable to begin with 
instead of a Comment.  I could run the iReport in the tool but not on the 
JasperSoft web client.

You can read more about it here.
http://community.jaspersoft.com/wiki/jaspersoft-security-changes-and-configuration

Janie

On Thu, Feb 20, 2014 at 11:59 AM, Charlie Lotridge 
mailto:lotri...@mcs-sf.com>> wrote:
**
I use quite a lot of SQL in my workflow, yet somehow never discovered this one 
before.  It turns out that if you're using SQL to pull data back for a Set 
Fields action, it must begin with the SELECT keyword, or it won't return any 
results.

For example, if you have a Set Fields with this SQL:

SELECT name
FROM arschema

it'll work fine.  But if you insert a comment before it:

-- Comment
SELECT name
FROM arschema

or even

/* Comment */ SELECT name
FROM arschema

the Set Fields will operate as if "No Request Match" (i.e. it'll display the No 
Match error, or set the target fields to NULL, depending upon how you've got it 
configured).

What's interesting here is that the SQL

Re: FYI...SQL in Set Fields MUST begin with "SELECT"

[Joe D'Souza]

It was a lot less than 400 characters on my SQL statement from what I
recall. More like 512 or less. I recall contacting Remedy Support regarding
that and if I remember right they came back with its as designed. I do not
recall what resolution I had to resort to, but I'm guessing I might have
gone with a view form or something to complete my set field action.

 

Joe

 

  _  

From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Charlie Lotridge
Sent: Thursday, February 20, 2014 6:29 PM
To: arslist@ARSLIST.ORG
Subject: Re: FYI...SQL in Set Fields MUST begin with "SELECT"

 

** 

Yeah, things start to get funny when you surpass the varchar limit
(typically 4000 chars) and head into LOB territory.  Maybe it had something
to do with that.

 

I've never used such a large query in a Set Fields, so I don't think I've
ever seen this issue.

 

-charlie

 

On Thu, Feb 20, 2014 at 3:21 PM, Joe D'Souza  wrote:

** 

I've once hit a limitation wherein a long SQL command (which ran perfectly
on the SQL client) didn't work within the SQL Set Fields action and returned
an error (do not recall what error as this was many years ago). So since
then I have tried to limit my use of SQL in Set Fields only if the number of
fields you are returning back are few. I do not know if this limitation is
now not an issue. If I recall right, I hit this limitation in a very early
release of 7.0 and I recall testing it on 6.3 and found that to be true on
that version too.

 

If I recall right, the limitation was not because of the number of fields
being returned, but the actual string length of the SQL statement.

 

Joe

 

  _  

From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Charlie Lotridge
Sent: Thursday, February 20, 2014 3:25 PM


To: arslist@ARSLIST.ORG
Subject: Re: FYI...SQL in Set Fields MUST begin with "SELECT"

 

** 

Interesting, thanks for this info.

 

I suppose this could be what's going on, but I'd think it would be more
appropriate for ARS to error if the SQL violates some rules, rather than
operate as if the query returned no results.  It's misleading.  Also, the
documentation mentions nothing about any such rules.

 

That Jaspersoft doc mentions that the SQL "should start with SELECT" and
"cannot have comments".  And while Remedy seems to be adhering to the first
part of this, it's not adhering to the second: I was able to insert comments
both as separate lines, and at the end of lines, and it works correctly.

 

-charlie

 

On Thu, Feb 20, 2014 at 12:12 PM, Janie Sprenger 
wrote:

** 

I think there are security requirements for web applications and one of the
requirements is to prevent SQL injection.  Not sure, but perhaps Remedy is
using something of this sort with the midtier. 

 

I ran into something similar with iReports and Jaspersoft when I was writing
an SQL query only mine happened to be with setting a variable to begin with
instead of a Comment.  I could run the iReport in the tool but not on the
JasperSoft web client.  

 

You can read more about it here. 

http://community.jaspersoft.com/wiki/jaspersoft-security-changes-and-configu
ration

Janie

 

On Thu, Feb 20, 2014 at 11:59 AM, Charlie Lotridge 
wrote:

** 

I use quite a lot of SQL in my workflow, yet somehow never discovered this
one before.  It turns out that if you're using SQL to pull data back for a
Set Fields action, it must begin with the SELECT keyword, or it won't return
any results.

 

For example, if you have a Set Fields with this SQL:

 

SELECT name

FROM arschema

 

it'll work fine.  But if you insert a comment before it:

 

-- Comment

SELECT name

FROM arschema

 

or even

 

/* Comment */ SELECT name

FROM arschema

 

the Set Fields will operate as if "No Request Match" (i.e. it'll display the
No Match error, or set the target fields to NULL, depending upon how you've
got it configured).

 

What's interesting here is that the SQL in these queries is syntactically
correct and they're submitted to the database by ARS without any error.  If
you submit the SQL manually (through SQL Plus or SQL Server Management
Studio, etc), it works correctly and returns the expected data.  Apparently,
though, Remedy doesn't know how to deal with it if it doesn't begin with the
keyword SELECT.

 

I only just discovered this because I was attempting to use a query
containing a WITH clause in SQL Server to create a Common Table Expression
to flatten out a recursive data structure.  Using the WITH clause, which
MUST be first in the query (and can't be contained in a subquery) is the
only way to do this in a single query.

 

Of course, the work-around is to create a view containing the CTE, which is
what I ultimately had to do.  It's just a less convenient solution.

 

Anyway, ju

Re: FYI...SQL in Set Fields MUST begin with "SELECT"

[Charlie Lotridge]

Yeah, things start to get funny when you surpass the varchar limit
(typically 4000 chars) and head into LOB territory.  Maybe it had something
to do with that.

I've never used such a large query in a Set Fields, so I don't think I've
ever seen this issue.

-charlie


On Thu, Feb 20, 2014 at 3:21 PM, Joe D'Souza  wrote:

> **
>
> I've once hit a limitation wherein a long SQL command (which ran perfectly
> on the SQL client) didn't work within the SQL Set Fields action and
> returned an error (do not recall what error as this was many years ago). So
> since then I have tried to limit my use of SQL in Set Fields only if the
> number of fields you are returning back are few. I do not know if this
> limitation is now not an issue. If I recall right, I hit this limitation in
> a very early release of 7.0 and I recall testing it on 6.3 and found that
> to be true on that version too.
>
>
>
> If I recall right, the limitation was not because of the number of fields
> being returned, but the actual string length of the SQL statement.
>
>
>
> Joe
>
>
>  --
>
> *From:* Action Request System discussion list(ARSList) [mailto:
> arslist@ARSLIST.ORG] *On Behalf Of *Charlie Lotridge
> *Sent:* Thursday, February 20, 2014 3:25 PM
>
> *To:* arslist@ARSLIST.ORG
> *Subject:* Re: FYI...SQL in Set Fields MUST begin with "SELECT"
>
>
>
> **
>
> Interesting, thanks for this info.
>
>
>
> I suppose this could be what's going on, but I'd think it would be more
> appropriate for ARS to error if the SQL violates some rules, rather than
> operate as if the query returned no results.  It's misleading.  Also, the
> documentation mentions nothing about any such rules.
>
>
>
> That Jaspersoft doc mentions that the SQL "should start with SELECT" and 
> "cannot
> have comments".  And while Remedy seems to be adhering to the first part of
> this, it's not adhering to the second: I was able to insert comments both
> as separate lines, and at the end of lines, and it works correctly.
>
>
>
> -charlie
>
>
>
> On Thu, Feb 20, 2014 at 12:12 PM, Janie Sprenger 
> wrote:
>
> **
>
> I think there are security requirements for web applications and one of
> the requirements is to prevent SQL injection.  Not sure, but perhaps Remedy
> is using something of this sort with the midtier.
>
>
>
> I ran into something similar with iReports and Jaspersoft when I was
> writing an SQL query only mine happened to be with setting a variable to
> begin with instead of a Comment.  I could run the iReport in the tool but
> not on the JasperSoft web client.
>
>
>
> You can read more about it here.
>
>
> http://community.jaspersoft.com/wiki/jaspersoft-security-changes-and-configuration
>
> Janie
>
>
>
> On Thu, Feb 20, 2014 at 11:59 AM, Charlie Lotridge 
> wrote:
>
> **
>
> I use quite a lot of SQL in my workflow, yet somehow never discovered this
> one before.  It turns out that if you're using SQL to pull data back for a
> Set Fields action, it must begin with the SELECT keyword, or it won't
> return any results.
>
>
>
> For example, if you have a Set Fields with this SQL:
>
>
>
> *SELECT name*
>
> *FROM arschema*
>
>
>
> it'll work fine.  But if you insert a comment before it:
>
>
>
> *-- Comment*
>
> *SELECT name*
>
> *FROM arschema*
>
>
>
> or even
>
>
>
> */* Comment */ SELECT name*
>
> *FROM arschema*
>
>
>
> the Set Fields will operate as if "No Request Match" (i.e. it'll display
> the No Match error, or set the target fields to NULL, depending upon how
> you've got it configured).
>
>
>
> What's interesting here is that the SQL in these queries is syntactically
> correct and they're submitted to the database by ARS without any error.  If
> you submit the SQL manually (through SQL Plus or SQL Server Management
> Studio, etc), it works correctly and returns the expected data.
>  Apparently, though, Remedy doesn't know how to deal with it if it doesn't
> begin with the keyword SELECT.
>
>
>
> I only just discovered this because I was attempting to use a query
> containing a WITH clause in SQL Server to create a Common Table Expression
> to flatten out a recursive data structure.  Using the WITH clause, which
> MUST be first in the query (and can't be contained in a subquery) is the
> only way to do this in a single query.
>
>
>
> Of course, the work-around is to create a view containing the CTE, which
> is what I ultimately had to do.  It's just a less convenient solution.
>
>
>
> Anyway, just something interesting I just discovered.
>
>
>
> -charlie
>
> _ARSlist: "Where the Answers Are" and have been for 20 years_
>
>
> _ARSlist: "Where the Answers Are" and have been for 20 years_
>
>
>
> _ARSlist: "Where the Answers Are" and have been for 20 years_
>  _ARSlist: "Where the Answers Are" and have been for 20 years_
>

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"

Re: FYI...SQL in Set Fields MUST begin with "SELECT"

[Joe D'Souza]

I've once hit a limitation wherein a long SQL command (which ran perfectly
on the SQL client) didn't work within the SQL Set Fields action and returned
an error (do not recall what error as this was many years ago). So since
then I have tried to limit my use of SQL in Set Fields only if the number of
fields you are returning back are few. I do not know if this limitation is
now not an issue. If I recall right, I hit this limitation in a very early
release of 7.0 and I recall testing it on 6.3 and found that to be true on
that version too.

 

If I recall right, the limitation was not because of the number of fields
being returned, but the actual string length of the SQL statement.

 

Joe

 

  _  

From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Charlie Lotridge
Sent: Thursday, February 20, 2014 3:25 PM
To: arslist@ARSLIST.ORG
Subject: Re: FYI...SQL in Set Fields MUST begin with "SELECT"

 

** 

Interesting, thanks for this info.

 

I suppose this could be what's going on, but I'd think it would be more
appropriate for ARS to error if the SQL violates some rules, rather than
operate as if the query returned no results.  It's misleading.  Also, the
documentation mentions nothing about any such rules.

 

That Jaspersoft doc mentions that the SQL "should start with SELECT" and
"cannot have comments".  And while Remedy seems to be adhering to the first
part of this, it's not adhering to the second: I was able to insert comments
both as separate lines, and at the end of lines, and it works correctly.

 

-charlie

 

On Thu, Feb 20, 2014 at 12:12 PM, Janie Sprenger 
wrote:

** 

I think there are security requirements for web applications and one of the
requirements is to prevent SQL injection.  Not sure, but perhaps Remedy is
using something of this sort with the midtier. 

 

I ran into something similar with iReports and Jaspersoft when I was writing
an SQL query only mine happened to be with setting a variable to begin with
instead of a Comment.  I could run the iReport in the tool but not on the
JasperSoft web client.  

 

You can read more about it here. 

http://community.jaspersoft.com/wiki/jaspersoft-security-changes-and-configu
ration

Janie

 

On Thu, Feb 20, 2014 at 11:59 AM, Charlie Lotridge 
wrote:

** 

I use quite a lot of SQL in my workflow, yet somehow never discovered this
one before.  It turns out that if you're using SQL to pull data back for a
Set Fields action, it must begin with the SELECT keyword, or it won't return
any results.

 

For example, if you have a Set Fields with this SQL:

 

SELECT name

FROM arschema

 

it'll work fine.  But if you insert a comment before it:

 

-- Comment

SELECT name

FROM arschema

 

or even

 

/* Comment */ SELECT name

FROM arschema

 

the Set Fields will operate as if "No Request Match" (i.e. it'll display the
No Match error, or set the target fields to NULL, depending upon how you've
got it configured).

 

What's interesting here is that the SQL in these queries is syntactically
correct and they're submitted to the database by ARS without any error.  If
you submit the SQL manually (through SQL Plus or SQL Server Management
Studio, etc), it works correctly and returns the expected data.  Apparently,
though, Remedy doesn't know how to deal with it if it doesn't begin with the
keyword SELECT.

 

I only just discovered this because I was attempting to use a query
containing a WITH clause in SQL Server to create a Common Table Expression
to flatten out a recursive data structure.  Using the WITH clause, which
MUST be first in the query (and can't be contained in a subquery) is the
only way to do this in a single query.

 

Of course, the work-around is to create a view containing the CTE, which is
what I ultimately had to do.  It's just a less convenient solution.

 

Anyway, just something interesting I just discovered.

 

-charlie

_ARSlist: "Where the Answers Are" and have been for 20 years_ 


_ARSlist: "Where the Answers Are" and have been for 20 years_ 

 

_ARSlist: "Where the Answers Are" and have been for 20 years_ 


___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"

Re: FYI...SQL in Set Fields MUST begin with "SELECT"

[Charlie Lotridge]

LJ, the doc indeed does say that there's no syntax checking, and I did
chase it down the rabbit hole insofar as to do a SQL log to confirm that
ARS thinks it's submitting the SQL to the DB.  Just to make sure, I then
cut & pasted that same SQL from the SQL log and into a query window and
executed it - it worked just as expected.

Shawn, WRT stored procedures, the
docsays
"A
stored procedure with a Set Fields action executes all its commands but
does *not* return a value."  If your stored procedure returns a result set
you'd like to use in a Set Fields then, again, the solution here would be
to wrap the call to the stored procedure in a SQL view and call that.  And,
again, it's a less convenient solution.


On Thu, Feb 20, 2014 at 12:30 PM, LJ LongWing  wrote:

> **
> the interesting thing is that what I remember of the docs state that
> Remedy does no syntax checking of your SQL to determine if it's accurate,
> it trusts you to do that.  And because of that, I must wonder if you traced
> the SQL all the way to the DB and determine what it's doing with thatto
> see if it's executing it or notnot sure what would be found if you went
> down that rabbit hole.
>
>
> On Thu, Feb 20, 2014 at 1:25 PM, Charlie Lotridge wrote:
>
>> **
>> Interesting, thanks for this info.
>>
>> I suppose this could be what's going on, but I'd think it would be more
>> appropriate for ARS to error if the SQL violates some rules, rather than
>> operate as if the query returned no results.  It's misleading.  Also, the
>> documentation mentions nothing about any such rules.
>>
>> That Jaspersoft doc mentions that the SQL "should start with SELECT" and
>> "cannot have comments".  And while Remedy seems to be adhering to the
>> first part of this, it's not adhering to the second: I was able to insert
>> comments both as separate lines, and at the end of lines, and it works
>> correctly.
>>
>> -charlie
>>
>>
>> On Thu, Feb 20, 2014 at 12:12 PM, Janie Sprenger wrote:
>>
>>> **
>>> I think there are security requirements for web applications and one of
>>> the requirements is to prevent SQL injection.  Not sure, but perhaps Remedy
>>> is using something of this sort with the midtier.
>>>
>>>  I ran into something similar with iReports and Jaspersoft when I was
>>> writing an SQL query only mine happened to be with setting a variable to
>>> begin with instead of a Comment.  I could run the iReport in the tool but
>>> not on the JasperSoft web client.
>>>
>>> You can read more about it here.
>>>
>>> http://community.jaspersoft.com/wiki/jaspersoft-security-changes-and-configuration
>>>
>>> Janie
>>>
>>> On Thu, Feb 20, 2014 at 11:59 AM, Charlie Lotridge 
>>> wrote:
>>>
 **
 I use quite a lot of SQL in my workflow, yet somehow never discovered
 this one before.  It turns out that if you're using SQL to pull data back
 for a Set Fields action, it must begin with the SELECT keyword, or it won't
 return any results.

 For example, if you have a Set Fields with this SQL:

 *SELECT name*
 *FROM arschema*

 it'll work fine.  But if you insert a comment before it:

 *-- Comment*
 *SELECT name*
 *FROM arschema*

 or even

 */* Comment */ SELECT name*
 *FROM arschema*

 the Set Fields will operate as if "No Request Match" (i.e. it'll
 display the No Match error, or set the target fields to NULL, depending
 upon how you've got it configured).

 What's interesting here is that the SQL in these queries is
 syntactically correct and they're submitted to the database by ARS without
 any error.  If you submit the SQL manually (through SQL Plus or SQL Server
 Management Studio, etc), it works correctly and returns the expected data.
  Apparently, though, Remedy doesn't know how to deal with it if it doesn't
 begin with the keyword SELECT.

 I only just discovered this because I was attempting to use a query
 containing a WITH clause in SQL Server to create a Common Table Expression
 to flatten out a recursive data structure.  Using the WITH clause, which
 MUST be first in the query (and can't be contained in a subquery) is the
 only way to do this in a single query.

 Of course, the work-around is to create a view containing the CTE,
 which is what I ultimately had to do.  It's just a less convenient 
 solution.

 Anyway, just something interesting I just discovered.

 -charlie
 _ARSlist: "Where the Answers Are" and have been for 20 years_
>>>
>>>
>>> _ARSlist: "Where the Answers Are" and have been for 20 years_
>>
>>
>> _ARSlist: "Where the Answers Are" and have been for 20 years_
>>
>
> _ARSlist: "Where the Answers Are" and have been for 20 years_
>

___
UNSUBSCRIBE or access ARSlist Archives at www.arslis

Re: FYI...SQL in Set Fields MUST begin with "SELECT"

[LJ LongWing]

the interesting thing is that what I remember of the docs state that Remedy
does no syntax checking of your SQL to determine if it's accurate, it
trusts you to do that.  And because of that, I must wonder if you traced
the SQL all the way to the DB and determine what it's doing with thatto
see if it's executing it or notnot sure what would be found if you went
down that rabbit hole.


On Thu, Feb 20, 2014 at 1:25 PM, Charlie Lotridge wrote:

> **
> Interesting, thanks for this info.
>
> I suppose this could be what's going on, but I'd think it would be more
> appropriate for ARS to error if the SQL violates some rules, rather than
> operate as if the query returned no results.  It's misleading.  Also, the
> documentation mentions nothing about any such rules.
>
> That Jaspersoft doc mentions that the SQL "should start with SELECT" and 
> "cannot
> have comments".  And while Remedy seems to be adhering to the first part of
> this, it's not adhering to the second: I was able to insert comments both
> as separate lines, and at the end of lines, and it works correctly.
>
> -charlie
>
>
> On Thu, Feb 20, 2014 at 12:12 PM, Janie Sprenger wrote:
>
>> **
>> I think there are security requirements for web applications and one of
>> the requirements is to prevent SQL injection.  Not sure, but perhaps Remedy
>> is using something of this sort with the midtier.
>>
>>  I ran into something similar with iReports and Jaspersoft when I was
>> writing an SQL query only mine happened to be with setting a variable to
>> begin with instead of a Comment.  I could run the iReport in the tool but
>> not on the JasperSoft web client.
>>
>> You can read more about it here.
>>
>> http://community.jaspersoft.com/wiki/jaspersoft-security-changes-and-configuration
>>
>> Janie
>>
>> On Thu, Feb 20, 2014 at 11:59 AM, Charlie Lotridge 
>> wrote:
>>
>>> **
>>> I use quite a lot of SQL in my workflow, yet somehow never discovered
>>> this one before.  It turns out that if you're using SQL to pull data back
>>> for a Set Fields action, it must begin with the SELECT keyword, or it won't
>>> return any results.
>>>
>>> For example, if you have a Set Fields with this SQL:
>>>
>>> *SELECT name*
>>> *FROM arschema*
>>>
>>> it'll work fine.  But if you insert a comment before it:
>>>
>>> *-- Comment*
>>> *SELECT name*
>>> *FROM arschema*
>>>
>>> or even
>>>
>>> */* Comment */ SELECT name*
>>> *FROM arschema*
>>>
>>> the Set Fields will operate as if "No Request Match" (i.e. it'll display
>>> the No Match error, or set the target fields to NULL, depending upon how
>>> you've got it configured).
>>>
>>> What's interesting here is that the SQL in these queries is
>>> syntactically correct and they're submitted to the database by ARS without
>>> any error.  If you submit the SQL manually (through SQL Plus or SQL Server
>>> Management Studio, etc), it works correctly and returns the expected data.
>>>  Apparently, though, Remedy doesn't know how to deal with it if it doesn't
>>> begin with the keyword SELECT.
>>>
>>> I only just discovered this because I was attempting to use a query
>>> containing a WITH clause in SQL Server to create a Common Table Expression
>>> to flatten out a recursive data structure.  Using the WITH clause, which
>>> MUST be first in the query (and can't be contained in a subquery) is the
>>> only way to do this in a single query.
>>>
>>> Of course, the work-around is to create a view containing the CTE, which
>>> is what I ultimately had to do.  It's just a less convenient solution.
>>>
>>> Anyway, just something interesting I just discovered.
>>>
>>> -charlie
>>> _ARSlist: "Where the Answers Are" and have been for 20 years_
>>
>>
>> _ARSlist: "Where the Answers Are" and have been for 20 years_
>
>
> _ARSlist: "Where the Answers Are" and have been for 20 years_
>

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"

Re: FYI...SQL in Set Fields MUST begin with "SELECT"

[Pierson, Shawn]

Many organizations build stored procedures for complex queries that you can 
execute instead of plain select statements because that SQL may be shared 
between multiple applications and it's easier to maintain in one place.  Do you 
know if this limitation (that I haven't seen any documentation on) would prefer 
execution of stored procedures?

Thanks,

Shawn Pierson
Remedy Developer | Energy Transfer

From: Action Request System discussion list(ARSList) 
[mailto:arslist@ARSLIST.ORG] On Behalf Of Charlie Lotridge
Sent: Thursday, February 20, 2014 2:25 PM
To: arslist@ARSLIST.ORG
Subject: Re: FYI...SQL in Set Fields MUST begin with "SELECT"

**
Interesting, thanks for this info.

I suppose this could be what's going on, but I'd think it would be more 
appropriate for ARS to error if the SQL violates some rules, rather than 
operate as if the query returned no results.  It's misleading.  Also, the 
documentation mentions nothing about any such rules.

That Jaspersoft doc mentions that the SQL "should start with SELECT" and 
"cannot have comments".  And while Remedy seems to be adhering to the first 
part of this, it's not adhering to the second: I was able to insert comments 
both as separate lines, and at the end of lines, and it works correctly.

-charlie

On Thu, Feb 20, 2014 at 12:12 PM, Janie Sprenger 
mailto:jrsrem...@gmail.com>> wrote:
**
I think there are security requirements for web applications and one of the 
requirements is to prevent SQL injection.  Not sure, but perhaps Remedy is 
using something of this sort with the midtier.

I ran into something similar with iReports and Jaspersoft when I was writing an 
SQL query only mine happened to be with setting a variable to begin with 
instead of a Comment.  I could run the iReport in the tool but not on the 
JasperSoft web client.

You can read more about it here.
http://community.jaspersoft.com/wiki/jaspersoft-security-changes-and-configuration

Janie

On Thu, Feb 20, 2014 at 11:59 AM, Charlie Lotridge 
mailto:lotri...@mcs-sf.com>> wrote:
**
I use quite a lot of SQL in my workflow, yet somehow never discovered this one 
before.  It turns out that if you're using SQL to pull data back for a Set 
Fields action, it must begin with the SELECT keyword, or it won't return any 
results.

For example, if you have a Set Fields with this SQL:

SELECT name
FROM arschema

it'll work fine.  But if you insert a comment before it:

-- Comment
SELECT name
FROM arschema

or even

/* Comment */ SELECT name
FROM arschema

the Set Fields will operate as if "No Request Match" (i.e. it'll display the No 
Match error, or set the target fields to NULL, depending upon how you've got it 
configured).

What's interesting here is that the SQL in these queries is syntactically 
correct and they're submitted to the database by ARS without any error.  If you 
submit the SQL manually (through SQL Plus or SQL Server Management Studio, 
etc), it works correctly and returns the expected data.  Apparently, though, 
Remedy doesn't know how to deal with it if it doesn't begin with the keyword 
SELECT.

I only just discovered this because I was attempting to use a query containing 
a WITH clause in SQL Server to create a Common Table Expression to flatten out 
a recursive data structure.  Using the WITH clause, which MUST be first in the 
query (and can't be contained in a subquery) is the only way to do this in a 
single query.

Of course, the work-around is to create a view containing the CTE, which is 
what I ultimately had to do.  It's just a less convenient solution.

Anyway, just something interesting I just discovered.

-charlie
_ARSlist: "Where the Answers Are" and have been for 20 years_

_ARSlist: "Where the Answers Are" and have been for 20 years_

_ARSlist: "Where the Answers Are" and have been for 20 years_

Private and confidential as detailed here: 
http://www.energytransfer.com/mail_disclaimer.aspx .  If you cannot access the 
link, please e-mail sender.

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"

Re: FYI...SQL in Set Fields MUST begin with "SELECT"

[Charlie Lotridge]

Interesting, thanks for this info.

I suppose this could be what's going on, but I'd think it would be more
appropriate for ARS to error if the SQL violates some rules, rather than
operate as if the query returned no results.  It's misleading.  Also, the
documentation mentions nothing about any such rules.

That Jaspersoft doc mentions that the SQL "should start with SELECT"
and "cannot
have comments".  And while Remedy seems to be adhering to the first part of
this, it's not adhering to the second: I was able to insert comments both
as separate lines, and at the end of lines, and it works correctly.

-charlie


On Thu, Feb 20, 2014 at 12:12 PM, Janie Sprenger wrote:

> **
> I think there are security requirements for web applications and one of
> the requirements is to prevent SQL injection.  Not sure, but perhaps Remedy
> is using something of this sort with the midtier.
>
> I ran into something similar with iReports and Jaspersoft when I was
> writing an SQL query only mine happened to be with setting a variable to
> begin with instead of a Comment.  I could run the iReport in the tool but
> not on the JasperSoft web client.
>
> You can read more about it here.
>
> http://community.jaspersoft.com/wiki/jaspersoft-security-changes-and-configuration
>
> Janie
>
> On Thu, Feb 20, 2014 at 11:59 AM, Charlie Lotridge wrote:
>
>> **
>> I use quite a lot of SQL in my workflow, yet somehow never discovered
>> this one before.  It turns out that if you're using SQL to pull data back
>> for a Set Fields action, it must begin with the SELECT keyword, or it won't
>> return any results.
>>
>> For example, if you have a Set Fields with this SQL:
>>
>> *SELECT name*
>> *FROM arschema*
>>
>> it'll work fine.  But if you insert a comment before it:
>>
>> *-- Comment*
>> *SELECT name*
>> *FROM arschema*
>>
>> or even
>>
>> */* Comment */ SELECT name*
>> *FROM arschema*
>>
>> the Set Fields will operate as if "No Request Match" (i.e. it'll display
>> the No Match error, or set the target fields to NULL, depending upon how
>> you've got it configured).
>>
>> What's interesting here is that the SQL in these queries is syntactically
>> correct and they're submitted to the database by ARS without any error.  If
>> you submit the SQL manually (through SQL Plus or SQL Server Management
>> Studio, etc), it works correctly and returns the expected data.
>>  Apparently, though, Remedy doesn't know how to deal with it if it doesn't
>> begin with the keyword SELECT.
>>
>> I only just discovered this because I was attempting to use a query
>> containing a WITH clause in SQL Server to create a Common Table Expression
>> to flatten out a recursive data structure.  Using the WITH clause, which
>> MUST be first in the query (and can't be contained in a subquery) is the
>> only way to do this in a single query.
>>
>> Of course, the work-around is to create a view containing the CTE, which
>> is what I ultimately had to do.  It's just a less convenient solution.
>>
>> Anyway, just something interesting I just discovered.
>>
>> -charlie
>> _ARSlist: "Where the Answers Are" and have been for 20 years_
>
>
> _ARSlist: "Where the Answers Are" and have been for 20 years_

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"

Re: FYI...SQL in Set Fields MUST begin with "SELECT"

[Janie Sprenger]

I think there are security requirements for web applications and one of the
requirements is to prevent SQL injection.  Not sure, but perhaps Remedy is
using something of this sort with the midtier.

I ran into something similar with iReports and Jaspersoft when I was
writing an SQL query only mine happened to be with setting a variable to
begin with instead of a Comment.  I could run the iReport in the tool but
not on the JasperSoft web client.

You can read more about it here.
http://community.jaspersoft.com/wiki/jaspersoft-security-changes-and-configuration

Janie

On Thu, Feb 20, 2014 at 11:59 AM, Charlie Lotridge wrote:

> **
> I use quite a lot of SQL in my workflow, yet somehow never discovered this
> one before.  It turns out that if you're using SQL to pull data back for a
> Set Fields action, it must begin with the SELECT keyword, or it won't
> return any results.
>
> For example, if you have a Set Fields with this SQL:
>
> *SELECT name*
> *FROM arschema*
>
> it'll work fine.  But if you insert a comment before it:
>
> *-- Comment*
> *SELECT name*
> *FROM arschema*
>
> or even
>
> */* Comment */ SELECT name*
> *FROM arschema*
>
> the Set Fields will operate as if "No Request Match" (i.e. it'll display
> the No Match error, or set the target fields to NULL, depending upon how
> you've got it configured).
>
> What's interesting here is that the SQL in these queries is syntactically
> correct and they're submitted to the database by ARS without any error.  If
> you submit the SQL manually (through SQL Plus or SQL Server Management
> Studio, etc), it works correctly and returns the expected data.
>  Apparently, though, Remedy doesn't know how to deal with it if it doesn't
> begin with the keyword SELECT.
>
> I only just discovered this because I was attempting to use a query
> containing a WITH clause in SQL Server to create a Common Table Expression
> to flatten out a recursive data structure.  Using the WITH clause, which
> MUST be first in the query (and can't be contained in a subquery) is the
> only way to do this in a single query.
>
> Of course, the work-around is to create a view containing the CTE, which
> is what I ultimately had to do.  It's just a less convenient solution.
>
> Anyway, just something interesting I just discovered.
>
> -charlie
> _ARSlist: "Where the Answers Are" and have been for 20 years_

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"

FYI...SQL in Set Fields MUST begin with "SELECT"

[Charlie Lotridge]

I use quite a lot of SQL in my workflow, yet somehow never discovered this
one before.  It turns out that if you're using SQL to pull data back for a
Set Fields action, it must begin with the SELECT keyword, or it won't
return any results.

For example, if you have a Set Fields with this SQL:

*SELECT name*
*FROM arschema*

it'll work fine.  But if you insert a comment before it:

*-- Comment*
*SELECT name*
*FROM arschema*

or even

*/* Comment */ SELECT name*
*FROM arschema*

the Set Fields will operate as if "No Request Match" (i.e. it'll display
the No Match error, or set the target fields to NULL, depending upon how
you've got it configured).

What's interesting here is that the SQL in these queries is syntactically
correct and they're submitted to the database by ARS without any error.  If
you submit the SQL manually (through SQL Plus or SQL Server Management
Studio, etc), it works correctly and returns the expected data.
 Apparently, though, Remedy doesn't know how to deal with it if it doesn't
begin with the keyword SELECT.

I only just discovered this because I was attempting to use a query
containing a WITH clause in SQL Server to create a Common Table Expression
to flatten out a recursive data structure.  Using the WITH clause, which
MUST be first in the query (and can't be contained in a subquery) is the
only way to do this in a single query.

Of course, the work-around is to create a view containing the CTE, which is
what I ultimately had to do.  It's just a less convenient solution.

Anyway, just something interesting I just discovered.

-charlie

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"