Re: [PHP] Security/Development Question
At 4:54 PM -0400 4/28/10, David Stoltz wrote: My concern is passing SQL queries in this way is not best practice - am I wrong? Please let me know how you would react to this? David : First, you are not wrong. Second, that's exactly the type of security risk you want to protect yourself from. Third, never trust anything coming from client-side (i.e., POST, GET, or COOKIE). Now, they (the vendor) can throw all the layers of confusion/nonsense (it's SSL, APS.NET, or will happen later) on this as they want, but the point remains this is permitting client-side access to a database and that is NOT good. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Security/Development Question
Hi all, I am learning PHP and found this problem to be interesting. I personally would never do this myself. All the manuals I have read strictly prohibit this type of behavior. Wouldn't you just have them run the queries on their end and send you the results instead of the query itself? Curious to see what would be the best scenario for this when you have a third party involved. What would be the best way to have them format their data to send you? Best, Karl On Apr 28, 2010, at 4:23 PM, Programming Guides wrote: On Wed, Apr 28, 2010 at 4:02 PM, Andre Polykanine wrote: Hello David, I'm not a PHP god but I would never ever do such things.I can't even imagine what can be the reason of passing an SQL query through a form... -- With best regards from Ukraine, Andre Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @ jabber.org Yahoo! messenger: andre.polykanine; ICQ: 191749952 Twitter: m_elensule - Original message - From: David Stoltz To: php-general@lists.php.net Date: Wednesday, April 28, 2010, 11:54:56 PM Subject: [PHP] Security/Development Question Hi folks, This isn't really a PHP question per se, but could apply to any language... I have a public facing web server, which we have a software component that helps protect us from SQL Injection, and the like. We recently have added a very small web application that is vendor supported. They said it's not working, so I investigated. I found that our software protection was blocking their pages because they are actually passing entire SQL queries in their form POSTs. Now, the app is SSL protected, and they claim the queries are not executed - only inserted into the database to be used later. They also said it's protected by the ASP.NET framework authenticationnot sure about any of that. My concern is passing SQL queries in this way is not best practice - am I wrong? Please let me know how you would react to this? See below for the stuff they are passing in the POST (obvious things like table names have been changed): /wEWBQLciq6UBwLEhISFCwLa2223bD3wK3+56LBAKc37iSDEsHMFjpB6o1vHld19wT +Tt3sY 8E&CRITICAL_RESULT&on&Declare @critical varchar (40) set @critical = (select top 1 code from table where id = 'clr7' and thename = 'critical') sELECT OPR_SECD.REC USER_REC_NO, RESULT.*, (SELECT RESULT_DESC FROM table WHERE code = RESULT.RES_MSTR_CODE) [DESC], [ORDER].*, (SELECT VALUE FROM table WHERE this_CODE = 'Email' AND USER_REC = OPR_SECD.RECNUM) MBMD_EMAIL, OPR_SECD.OPR_INITIAL FROM RESULTING LEFT JOIN [ORDER] ON RESULTING.ORDER_REC = [ORDERBY].RECNUM LEFT JOIN OPR_SECD ON [ORDER].DR_CODE = OPR_SECD.XREF_CODE where (RESULT.FLAG_TEXT) = @critical AND RESULT.REC = @ID&Save -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php I can't say that I agree with this design but it is certainly possible to prevent against crafted POST data. I think in your particular case they might be doing that judging by the presence of the hash at the beginning of the POST data (although that could be anything... I'm just guessing). A general way to prevent against crafted POST data is to have a session or even a page secret key. The key is hashed with the value which is then written to the (I suppose) hidden form field. When the POST data comes back it's hashed with the key and checked against the hash in the POST. So.. yes it's possible to prevent from crafting the POST data but the design is still crappy; I wouldn't do it. -- Viktor http://programming-guides.com Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Security/Development Question
On Wed, Apr 28, 2010 at 4:02 PM, Andre Polykanine wrote: > Hello David, > > I'm not a PHP god but I would never ever do such things.I can't even > imagine what can be the reason of passing an SQL query through a > form... > -- > With best regards from Ukraine, > Andre > Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @ > jabber.org > Yahoo! messenger: andre.polykanine; ICQ: 191749952 > Twitter: m_elensule > > - Original message - > From: David Stoltz > To: php-general@lists.php.net > Date: Wednesday, April 28, 2010, 11:54:56 PM > Subject: [PHP] Security/Development Question > > Hi folks, > > > > This isn't really a PHP question per se, but could apply to any > language... > > > > I have a public facing web server, which we have a software component > that helps protect us from SQL Injection, and the like. > > > > We recently have added a very small web application that is vendor > supported. They said it's not working, so I investigated. I found that > our software protection was blocking their pages because they are > actually passing entire SQL queries in their form POSTs. Now, the app is > SSL protected, and they claim the queries are not executed - only > inserted into the database to be used later. They also said it's > protected by the ASP.NET framework authenticationnot sure about any > of that. > > > > My concern is passing SQL queries in this way is not best practice - am > I wrong? Please let me know how you would react to this? > > > > See below for the stuff they are passing in the POST (obvious things > like table names have been changed): > > > > /wEWBQLciq6UBwLEhISFCwLa2223bD3wK3+56LBAKc37iSDEsHMFjpB6o1vHld19wT+Tt3sY > 8E&CRITICAL_RESULT&on&Declare @critical varchar (40) > > set @critical = (select top 1 code from table where id = 'clr7' and > thename = 'critical') > > > > sELECT > > OPR_SECD.REC USER_REC_NO, > > RESULT.*, > > (SELECT RESULT_DESC FROM table WHERE code = RESULT.RES_MSTR_CODE) > [DESC], > > [ORDER].*, > > (SELECT VALUE FROM table WHERE this_CODE = 'Email' AND USER_REC = > OPR_SECD.RECNUM) MBMD_EMAIL, > > OPR_SECD.OPR_INITIAL > > FROM RESULTING LEFT JOIN [ORDER] ON RESULTING.ORDER_REC = > [ORDERBY].RECNUM > > LEFT JOIN OPR_SECD ON [ORDER].DR_CODE = OPR_SECD.XREF_CODE > > where (RESULT.FLAG_TEXT) = @critical AND RESULT.REC = @ID&Save > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > I can't say that I agree with this design but it is certainly possible to prevent against crafted POST data. I think in your particular case they might be doing that judging by the presence of the hash at the beginning of the POST data (although that could be anything... I'm just guessing). A general way to prevent against crafted POST data is to have a session or even a page secret key. The key is hashed with the value which is then written to the (I suppose) hidden form field. When the POST data comes back it's hashed with the key and checked against the hash in the POST. So.. yes it's possible to prevent from crafting the POST data but the design is still crappy; I wouldn't do it. -- Viktor http://programming-guides.com
Re: [PHP] Security/Development Question
David Stoltz wrote: Hi folks, This isn't really a PHP question per se, but could apply to any language... I have a public facing web server, which we have a software component that helps protect us from SQL Injection, and the like. We recently have added a very small web application that is vendor supported. They said it's not working, so I investigated. I found that our software protection was blocking their pages because they are actually passing entire SQL queries in their form POSTs. Run... scream and run... to the server and get it off :) Ok, maybe it's not that bad... read on for more! Now, the app is SSL protected, and they claim the queries are not executed - only inserted into the database to be used later. TO BE USED LATER??? AS IN EXECUTED? It doesn't matter if you stave off an injection attack till a week from today, it still will screw up your database. They also said it's protected by the ASP.NET framework authenticationnot sure about any of that. Me neither... but I'd still be worried. Maybe the query was created on the server and a checksum or hash was createwd to ensure no tampering with the query. But still, I would worry that you are revealing information about your database to the public. No information leakage is the best kind of leakage. My concern is passing SQL queries in this way is not best practice - am I wrong? Please let me know how you would react to this? You are right. This is shoddy practice. See below for the stuff they are passing in the POST (obvious things like table names have been changed): /wEWBQLciq6UBwLEhISFCwLa2223bD3wK3+56LBAKc37iSDEsHMFjpB6o1vHld19wT+Tt3sY 8E The above portion is probably the checksum / hash to detect tampering... AKA if I understand correctly... the "ASP.NET framework authentication". &CRITICAL_RESULT&on&Declare @critical varchar (40) set @critical = (select top 1 code from table where id = 'clr7' and thename = 'critical') sELECT OPR_SECD.REC USER_REC_NO, RESULT.*, (SELECT RESULT_DESC FROM table WHERE code = RESULT.RES_MSTR_CODE) [DESC], [ORDER].*, (SELECT VALUE FROM table WHERE this_CODE = 'Email' AND USER_REC = OPR_SECD.RECNUM) MBMD_EMAIL, OPR_SECD.OPR_INITIAL FROM RESULTING LEFT JOIN [ORDER] ON RESULTING.ORDER_REC = [ORDERBY].RECNUM LEFT JOIN OPR_SECD ON [ORDER].DR_CODE = OPR_SECD.XREF_CODE where (RESULT.FLAG_TEXT) = @critical AND RESULT.REC = @ID&Save Nice of them to share the database structure with the public. The query may be secure from tampering, but it is terrible practice to reveal internal design to the public. Although, admittedly in this day and age of open source applications like drupal/joomla/mediawiki/other the public knows your database structure unless you choose an offbeat table prefix :) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Security/Development Question
On Wed, Apr 28, 2010 at 04:54:56PM -0400, David Stoltz wrote: > Hi folks, > > > > This isn't really a PHP question per se, but could apply to any > language... > > > > I have a public facing web server, which we have a software component > that helps protect us from SQL Injection, and the like. > > > > We recently have added a very small web application that is vendor > supported. They said it's not working, so I investigated. I found that > our software protection was blocking their pages because they are > actually passing entire SQL queries in their form POSTs. Now, the app is > SSL protected, and they claim the queries are not executed - only > inserted into the database to be used later. They also said it's > protected by the ASP.NET framework authenticationnot sure about any > of that. > > > > My concern is passing SQL queries in this way is not best practice - am > I wrong? Please let me know how you would react to this? > > > > See below for the stuff they are passing in the POST (obvious things > like table names have been changed): > > > > /wEWBQLciq6UBwLEhISFCwLa2223bD3wK3+56LBAKc37iSDEsHMFjpB6o1vHld19wT+Tt3sY > 8E&CRITICAL_RESULT&on&Declare @critical varchar (40) > > set @critical = (select top 1 code from table where id = 'clr7' and > thename = 'critical') > > > > sELECT > > OPR_SECD.REC USER_REC_NO, > > RESULT.*, > > (SELECT RESULT_DESC FROM table WHERE code = RESULT.RES_MSTR_CODE) > [DESC], > > [ORDER].*, > > (SELECT VALUE FROM table WHERE this_CODE = 'Email' AND USER_REC = > OPR_SECD.RECNUM) MBMD_EMAIL, > > OPR_SECD.OPR_INITIAL > > FROM RESULTING LEFT JOIN [ORDER] ON RESULTING.ORDER_REC = > [ORDERBY].RECNUM > > LEFT JOIN OPR_SECD ON [ORDER].DR_CODE = OPR_SECD.XREF_CODE > > where (RESULT.FLAG_TEXT) = @critical AND RESULT.REC = @ID&Save > Holy crap! There's a very good reason why your security application is blocking their software. You have no control over these SQL strings, and injection could occur if only by accident if you're not in control of them. The only thing I can imagine worse than passing a SQL query around in a POST variable is passing it around in a GET variable. Sometimes I wonder where programmers like this come from. I'm not that good with security, but even I wouldn't do this. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Security/Development Question
Hello David, I'm not a PHP god but I would never ever do such things.I can't even imagine what can be the reason of passing an SQL query through a form... -- With best regards from Ukraine, Andre Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @ jabber.org Yahoo! messenger: andre.polykanine; ICQ: 191749952 Twitter: m_elensule - Original message - From: David Stoltz To: php-general@lists.php.net Date: Wednesday, April 28, 2010, 11:54:56 PM Subject: [PHP] Security/Development Question Hi folks, This isn't really a PHP question per se, but could apply to any language... I have a public facing web server, which we have a software component that helps protect us from SQL Injection, and the like. We recently have added a very small web application that is vendor supported. They said it's not working, so I investigated. I found that our software protection was blocking their pages because they are actually passing entire SQL queries in their form POSTs. Now, the app is SSL protected, and they claim the queries are not executed - only inserted into the database to be used later. They also said it's protected by the ASP.NET framework authenticationnot sure about any of that. My concern is passing SQL queries in this way is not best practice - am I wrong? Please let me know how you would react to this? See below for the stuff they are passing in the POST (obvious things like table names have been changed): /wEWBQLciq6UBwLEhISFCwLa2223bD3wK3+56LBAKc37iSDEsHMFjpB6o1vHld19wT+Tt3sY 8E&CRITICAL_RESULT&on&Declare @critical varchar (40) set @critical = (select top 1 code from table where id = 'clr7' and thename = 'critical') sELECT OPR_SECD.REC USER_REC_NO, RESULT.*, (SELECT RESULT_DESC FROM table WHERE code = RESULT.RES_MSTR_CODE) [DESC], [ORDER].*, (SELECT VALUE FROM table WHERE this_CODE = 'Email' AND USER_REC = OPR_SECD.RECNUM) MBMD_EMAIL, OPR_SECD.OPR_INITIAL FROM RESULTING LEFT JOIN [ORDER] ON RESULTING.ORDER_REC = [ORDERBY].RECNUM LEFT JOIN OPR_SECD ON [ORDER].DR_CODE = OPR_SECD.XREF_CODE where (RESULT.FLAG_TEXT) = @critical AND RESULT.REC = @ID&Save -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
