2 /*********************************************************************************
3 * SugarCRM Community Edition is a customer relationship management program developed by
4 * SugarCRM, Inc. Copyright (C) 2004-2012 SugarCRM Inc.
6 * This program is free software; you can redistribute it and/or modify it under
7 * the terms of the GNU Affero General Public License version 3 as published by the
8 * Free Software Foundation with the addition of the following permission added
9 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
10 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
11 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
13 * This program is distributed in the hope that it will be useful, but WITHOUT
14 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
15 * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
18 * You should have received a copy of the GNU Affero General Public License along with
19 * this program; if not, see http://www.gnu.org/licenses or write to the Free
20 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
24 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
26 * The interactive user interfaces in modified source and object code versions
27 * of this program must display Appropriate Legal Notices, as required under
28 * Section 5 of the GNU Affero General Public License version 3.
30 * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
31 * these Appropriate Legal Notices must retain the display of the "Powered by
32 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
33 * technical reasons, the Appropriate Legal Notices must display the words
34 * "Powered by SugarCRM".
35 ********************************************************************************/
38 require_once 'include/SugarSQLValidate.php';
40 class QuerySanitizeTest extends Sugar_PHPUnit_Framework_TestCase
43 public function getQueries()
47 array("", "accounts.name", true),
48 array("", "something BAD", false),
49 array("", "something BAD", false),
50 array("accounts.name like 'Underwater%'", "", true),
51 array("name like 'Underwater%'", "accounts.name", true),
52 array("name like 'Underwater%' AND MONTH(accounts.date_created) < MONTH(opportunities.date_modified)+1", "date_created DESC, lcase(account.name) ASC", true),
53 array("accounts.name like 'Underwater%'", "something BAD", false),
54 array("accounts.name like 'Underwater%'", "also, something BAD", false),
55 array("z=1 UNION SELECT * from users", "", false),
56 array("z=1 UNION ALL SELECT * from users", "", false),
57 array("z=1 UNION ALL SELECT * from users#", "", false),
58 array("z=1 UNION ALL SELECT * from users -- test", "", false),
59 array("", "something BAD", false),
60 array("id='' AND 1=0 UNION SELECT from_addr,1,to_addrs,description FROM emails_text LIMIT 1#", "", false),
61 array("", "foo UNION ALL SELECT * from users", false),
62 array("", "(leads.status='' OR leads.status IS NULL) DESC,leads.status='New' DESC,leads.status='Assigned' DESC,leads.status='In Process' DESC,leads.status='Converted' DESC,leads.status='Recycled' DESC,leads.status='Dead' DESC", true),
63 // OPI email query, should pass
64 array("contacts.assigned_user_id = '1' AND (contacts.first_name like '%collin.c.lee@gmail.com%' OR contacts.last_name like '%collin.c.lee@gmail.com%' OR contacts.id IN (SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 AND ea.email_address LIKE 'collin.c.lee@gmail.com%'))", "contacts.last_name asc", true),
65 // Evil subselect, should not pass
66 array("1=1 AND EXISTS (SELECT * FROM users WHERE is_admin=1 and id=(select id from users where is_admin=1 order by id limit 1) and ((ord(substring(id, 1, 1)) >> 5) & 1))", "", false),
67 // OPI email query with evil mods, should not pass
68 array("contacts.assigned_user_id = '1' AND (contacts.first_name like '%collin.c.lee@gmail.com%' OR contacts.last_name like '%collin.c.lee@gmail.com%' OR contacts.id IN (SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) JOIN users WHERE users.is_admin='1' AND eabr.deleted=0 AND ea.email_address LIKE 'collin.c.lee@gmail.com%'))", "contacts.last_name asc", false),
70 array('contacts.id IN (SELECT email_addr_bean_rel.bean_id FROM email_addr_bean_rel, email_addresses WHERE email_addresses.id = email_addr_bean_rel.email_address_id AND email_addr_bean_rel.deleted = 0 AND email_addr_bean_rel.bean_module = \'Contacts\' AND email_addresses.email_address IN ("odemendez@starbucks.fr"))', '', true),
71 // bug 50487 - Quoted identifiers
72 array("`users`.`user_name` = 'admin'", "", true),
73 array("`users`.`user_name` = 'admin' and `users`.`first_name` = 'george'", "", true),
74 array("`users`.`user_name` = 'admin' and `users`.`first_name` = 'george'", "`users`.`first_name`", true),
75 array("`users.user_name = 'admin'`", "", false),
80 * @dataProvider getQueries
81 * @outputBuffering disabled
83 public function testCheckQuery($where, $order_by, $ok)
85 $helper = new SugarSQLValidate();
86 $res = $helper->validateQueryClauses($where, $order_by);
87 $params = array($where, $order_by);
89 $this->assertTrue($res, string_format("Failed asserting that where: {0} and order by: {1} is valid", $params));
91 $this->assertFalse($res, string_format("Failed asserting that where: {0} and order by: {1} is invalid", $params));