FYI...SQL in Set Fields MUST begin with SELECT
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
Re: FYI...SQL in Set Fields MUST begin with SELECT
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 lotri...@mcs-sf.comwrote: ** 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
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 jrsrem...@gmail.comwrote: ** 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 lotri...@mcs-sf.comwrote: ** 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
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 jrsrem...@gmail.commailto: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 lotri...@mcs-sf.commailto: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
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 lotri...@mcs-sf.comwrote: ** 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 jrsrem...@gmail.comwrote: ** 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 lotri...@mcs-sf.comwrote: ** 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
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 dochttps://docs.bmc.com/docs/display/public/ars81/Assigning+values+through+SQL+statementssays 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 lj.longw...@gmail.com 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 lotri...@mcs-sf.comwrote: ** 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 jrsrem...@gmail.comwrote: ** 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 lotri...@mcs-sf.comwrote: ** 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
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 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-configu ration Janie On Thu, Feb 20, 2014 at 11:59 AM, Charlie Lotridge 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_ ___ 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
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 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] *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 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 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_ _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
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 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] 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 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-configu ration Janie On Thu, Feb 20, 2014 at 11:59 AM, Charlie Lotridge 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
Re: FYI...SQL in Set Fields MUST begin with SELECT
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.ORGmailto: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
jdso...@shyle.netmailto: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.ORGmailto:arslist@ARSLIST.ORG] On Behalf Of Charlie
Lotridge
Sent: Thursday, February 20, 2014 3:25 PM
To: arslist@ARSLIST.ORGmailto: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
jrsrem...@gmail.commailto: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
lotri...@mcs-sf.commailto: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
Re: FYI...SQL in Set Fields MUST begin with SELECT
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 lotri...@mcs-sf.com 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 lj.longw...@gmail.com 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 lotri...@mcs-sf.com 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 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 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_ _ARSlist: Where the Answers Are and have been for 20 years_ _ARSlist: Where the Answers Are and have been for 20 years_
