Matthew, OR, you just have the SQL command defined like
$WHERE_CLAUSE$ and then when you finish building your WHERE clause like you are today, you simply set the WHERE_CLAUSE field to "SELECT C1 FROM T1 WHERE " + $WHERE_CLAUSE$ and then you own and control the entire syntax of the SQL yourself, you have built the command you want. And you execute it. The issue is a matter of having the system protect you and do all the handling of protecting quotes in values (like if the name being substituted was o'malley) or one where you want to take all responsibility for formatting and making sure the command is right. We support either option. You don't have to go the direction of putting the clause in a table and using a stored procedure. You can just build the entire command yourself instead of building just 1/2 of it. Just another option that is a bit simpler and more efficient. Doug Mueller -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Matthew Perrault Sent: Monday, September 27, 2010 11:42 AM To: [email protected] Subject: Re: Set Fields from SQL The only issue I have with this, Is I have a filter that has a Direct SQL action. In it I specify the SQL command like: SELECT C1 FROM T1 Where '$WHERE_CLAUSE$' I dynamically build the where clause and set it into that field. So I have something like Name = 'shmoe' and eye_color = 'Blue' Unfortunately the ARS engine comments out the 'shmoe' and 'Blue' To look like ''shmoe'' and ''Blue'' This then causes the SQL to fail. What I was forced to do was drop the Where Clause into a SQL table, Build the SELECT statement through a Stored Procedure using an EXEC statement. It works but it was a cludgy way of doing things. I would love for on the DIRECT SQL action, a check box to say turn off Commentation, So that it won't add those extra quotes. I did submit a request, but of course BMC just rejected it. Thanks guys.... Matt P. -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Opela, Gary L CTR USAF ABW 72 ABW/SCOOA Sent: Monday, September 27, 2010 12:32 PM To: [email protected] Subject: Re: Set Fields from SQL Roy, if you have more information on this, you can look up 'SQL Injection'. Thanks, Gary Opela, Jr. Sr. Remedy Engineer Avaya Phone Admin RSP Cert, Sec+ COMM: 405 582 4272 -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Mueller, Doug Sent: Monday, September 27, 2010 12:27 PM To: [email protected] Subject: Re: Set Fields from SQL Roy, What you are seeing is what is designed. We protect you against illegal SQL or someone trying to play with breaking your SQL by sticking SQL inside other SQL by properly protecting against any bad SQL by always quoting any text substituted into the command. This prevents anyone from mis-appropriating your SQL command with bad text. Now, you also found that we gave you an out. If you as Administrator make the ENTIRE SQL command a substitution, then we give up and say you are substituting the entire command so you must know what you are doing and we assume you are doing whatever protecting from bad SQL the customer may do. For those who wonder "what does he mean bad sql"? What if you had a command like the following: SELECT a FROM b WHERE ColName = '$subs parameter$' If the user would enter aa'; DROP TABLE xx; Select a from b where ColName = 'xyz as the data value for subs paramter.... They can "complete the command", issue another SQL command, and then do something so that the command is valid again to avoid an error. This would allow bad SQL to be submitted by the customer. By always quoting and escaping any user quotes, we prevent that. If you create the entire command yourself, you have to protect against the end user entering text that is like the above that could affect your SQL command syntax.... I hope this explaination helps with why the system works the way it does. Doug Mueller -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Ashcraft, Roy W CTR USAF AFWA 2 SYOS/SYOE Sent: Wednesday, September 22, 2010 7:07 AM To: [email protected] Subject: Re: Set Fields from SQL Never mind. A coworker suggested trying to build the enter SQL query in a separate character field and then substitute that into the SQL set fields rather than building it piecemeal there. That worked, it did not escape the single quotes using this action. Thanks, Roy //SIGNED// ROY ASHCRAFT, Contractor, 2 SOS/SYOE Remedy ARS Support, SAIC (402) 294-8225, DSN 271-8225 [email protected] -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Ashcraft, Roy W CTR USAF AFWA 2 SYOS/SYOE Sent: Wednesday, September 22, 2010 8:59 AM To: [email protected] Subject: Set Fields from SQL ---------------------- Information from the mail header ----------------------- Sender: "Action Request System discussion list(ARSList)" ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
