001    /**
002     * Copyright (c) 2000-2012 Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.portal.security.pacl.checker;
016    
017    import com.liferay.portal.kernel.log.Log;
018    import com.liferay.portal.kernel.log.LogFactoryUtil;
019    
020    import java.io.StringReader;
021    
022    import java.security.Permission;
023    
024    import java.util.ArrayList;
025    import java.util.List;
026    import java.util.Set;
027    
028    import net.sf.jsqlparser.expression.Expression;
029    import net.sf.jsqlparser.expression.operators.relational.ItemsList;
030    import net.sf.jsqlparser.parser.CCJSqlParserManager;
031    import net.sf.jsqlparser.parser.JSqlParser;
032    import net.sf.jsqlparser.schema.Table;
033    import net.sf.jsqlparser.statement.Statement;
034    import net.sf.jsqlparser.statement.create.table.CreateTable;
035    import net.sf.jsqlparser.statement.delete.Delete;
036    import net.sf.jsqlparser.statement.drop.Drop;
037    import net.sf.jsqlparser.statement.insert.Insert;
038    import net.sf.jsqlparser.statement.replace.Replace;
039    import net.sf.jsqlparser.statement.select.Select;
040    import net.sf.jsqlparser.statement.select.SelectBody;
041    import net.sf.jsqlparser.statement.truncate.Truncate;
042    import net.sf.jsqlparser.statement.update.Update;
043    import net.sf.jsqlparser.test.tablesfinder.TablesNamesFinder;
044    
045    /**
046     * @author Brian Wing Shun Chan
047     */
048    public class SQLChecker extends BaseChecker {
049    
050            public void afterPropertiesSet() {
051                    initTableNames();
052            }
053    
054            public void checkPermission(Permission permission) {
055                    throw new UnsupportedOperationException();
056            }
057    
058            public boolean hasSQL(String sql) {
059                    Statement statement = null;
060    
061                    try {
062                            statement = _jSqlParser.parse(new StringReader(sql));
063                    }
064                    catch (Exception e) {
065                            _log.error("Unable to parse SQL " + sql);
066    
067                            return false;
068                    }
069    
070                    if (statement instanceof CreateTable) {
071                            CreateTable createTable = (CreateTable)statement;
072    
073                            return hasSQL(createTable);
074                    }
075                    else if (statement instanceof Select) {
076                            Select select = (Select)statement;
077    
078                            return hasSQL(select);
079                    }
080                    else if (statement instanceof Delete) {
081                            Delete delete = (Delete)statement;
082    
083                            return hasSQL(delete);
084                    }
085                    else if (statement instanceof Drop) {
086                            Drop drop = (Drop)statement;
087    
088                            return hasSQL(drop);
089                    }
090                    else if (statement instanceof Insert) {
091                            Insert insert = (Insert)statement;
092    
093                            return hasSQL(insert);
094                    }
095                    else if (statement instanceof Replace) {
096                            Replace replace = (Replace)statement;
097    
098                            return hasSQL(replace);
099                    }
100                    else if (statement instanceof Select) {
101                            Select select = (Select)statement;
102    
103                            return hasSQL(select);
104                    }
105                    else if (statement instanceof Truncate) {
106                            Truncate truncate = (Truncate)statement;
107    
108                            return hasSQL(truncate);
109                    }
110                    else if (statement instanceof Update) {
111                            Update update = (Update)statement;
112    
113                            return hasSQL(update);
114                    }
115    
116                    return false;
117            }
118    
119            protected boolean hasSQL(CreateTable createTable) {
120                    return isAllowedTable(createTable.getTable(), _createTableNames);
121            }
122    
123            protected boolean hasSQL(Delete delete) {
124                    TableNamesFinder tableNamesFinder = new TableNamesFinder();
125    
126                    List<String> tableNames = tableNamesFinder.getTableNames(delete);
127    
128                    return isAllowedTables(tableNames, _deleteTableNames);
129            }
130    
131            protected boolean hasSQL(Drop drop) {
132                    return isAllowedTable(drop.getName(), _dropTableNames);
133            }
134    
135            protected boolean hasSQL(Insert insert) {
136                    TableNamesFinder tableNamesFinder = new TableNamesFinder();
137    
138                    List<String> tableNames = tableNamesFinder.getTableNames(insert);
139    
140                    return isAllowedTables(tableNames, _insertTableNames);
141            }
142    
143            protected boolean hasSQL(Replace replace) {
144                    TableNamesFinder tableNamesFinder = new TableNamesFinder();
145    
146                    List<String> tableNames = tableNamesFinder.getTableNames(replace);
147    
148                    return isAllowedTables(tableNames, _replaceTableNames);
149            }
150    
151            protected boolean hasSQL(Select select) {
152                    TableNamesFinder tableNamesFinder = new TableNamesFinder();
153    
154                    List<String> tableNames = tableNamesFinder.getTableNames(select);
155    
156                    return isAllowedTables(tableNames, _selectTableNames);
157            }
158    
159            protected boolean hasSQL(Truncate truncate) {
160                    return isAllowedTable(truncate.getTable(), _truncateTableNames);
161            }
162    
163            protected boolean hasSQL(Update update) {
164                    TableNamesFinder tableNamesFinder = new TableNamesFinder();
165    
166                    List<String> tableNames = tableNamesFinder.getTableNames(update);
167    
168                    return isAllowedTables(tableNames, _updateTableNames);
169            }
170    
171            protected void initTableNames() {
172                    _allTableNames = getPropertySet("security-manager-sql-tables-all");
173                    _createTableNames = getPropertySet(
174                            "security-manager-sql-tables-create");
175                    _deleteTableNames = getPropertySet(
176                            "security-manager-sql-tables-delete");
177                    _dropTableNames = getPropertySet("security-manager-sql-tables-drop");
178                    _insertTableNames = getPropertySet(
179                            "security-manager-sql-tables-insert");
180                    _replaceTableNames = getPropertySet(
181                            "security-manager-sql-tables-replace");
182                    _selectTableNames = getPropertySet(
183                            "security-manager-sql-tables-select");
184                    _truncateTableNames = getPropertySet(
185                            "security-manager-sql-tables-truncate");
186                    _updateTableNames = getPropertySet(
187                            "security-manager-sql-tables-update");
188            }
189    
190            protected boolean isAllowedTable(
191                    String tableName, Set<String> allowedTableNames) {
192    
193                    if (_allTableNames.contains(tableName) ||
194                            allowedTableNames.contains(tableName)) {
195    
196                            return true;
197                    }
198    
199                    return false;
200            }
201    
202            protected boolean isAllowedTable(
203                    Table table, Set<String> allowedTableNames) {
204    
205                    String tableName = table.getName();
206    
207                    return isAllowedTable(tableName, allowedTableNames);
208            }
209    
210            protected boolean isAllowedTables(
211                    List<String> tableNames, Set<String> allowedTableNames) {
212    
213                    for (String tableName : tableNames) {
214                            if (!isAllowedTable(tableName, allowedTableNames)) {
215                                    return false;
216                            }
217                    }
218    
219                    return true;
220            }
221    
222            private static Log _log = LogFactoryUtil.getLog(SQLChecker.class);
223    
224            private Set<String> _allTableNames;
225            private Set<String> _createTableNames;
226            private Set<String> _deleteTableNames;
227            private Set<String> _dropTableNames;
228            private Set<String> _insertTableNames;
229            private JSqlParser _jSqlParser = new CCJSqlParserManager();
230            private Set<String> _replaceTableNames;
231            private Set<String> _selectTableNames;
232            private Set<String> _truncateTableNames;
233            private Set<String> _updateTableNames;
234    
235            private class TableNamesFinder extends TablesNamesFinder {
236    
237                    public TableNamesFinder() {
238                            tables = new ArrayList<String>();
239                    }
240    
241                    public List<String> getTableNames(Delete delete) {
242                            Table table = delete.getTable();
243    
244                            tables.add(table.getName());
245    
246                            Expression where = delete.getWhere();
247    
248                            where.accept(this);
249    
250                            return tables;
251                    }
252    
253                    public List<String> getTableNames(Insert insert) {
254                            Table table = insert.getTable();
255    
256                            tables.add(table.getName());
257    
258                            ItemsList itemsList = insert.getItemsList();
259    
260                            itemsList.accept(this);
261    
262                            return tables;
263                    }
264    
265                    public List<String> getTableNames(Replace replace) {
266                            Table table = replace.getTable();
267    
268                            tables.add(table.getName());
269    
270                            ItemsList itemsList = replace.getItemsList();
271    
272                            itemsList.accept(this);
273    
274                            return tables;
275                    }
276    
277                    public List<String> getTableNames(Select select) {
278                            SelectBody selectBody = select.getSelectBody();
279    
280                            selectBody.accept(this);
281    
282                            return tables;
283                    }
284    
285                    public List<String> getTableNames(Update update) {
286                            Table table = update.getTable();
287    
288                            tables.add(table.getName());
289    
290                            Expression where = update.getWhere();
291    
292                            where.accept(this);
293    
294                            return tables;
295                    }
296    
297            }
298    
299    }