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    import com.liferay.portal.kernel.util.StringUtil;
020    
021    import java.io.StringReader;
022    
023    import java.security.Permission;
024    
025    import java.util.ArrayList;
026    import java.util.List;
027    import java.util.Set;
028    
029    import net.sf.jsqlparser.expression.Expression;
030    import net.sf.jsqlparser.expression.operators.relational.ItemsList;
031    import net.sf.jsqlparser.parser.CCJSqlParserManager;
032    import net.sf.jsqlparser.parser.JSqlParser;
033    import net.sf.jsqlparser.schema.Table;
034    import net.sf.jsqlparser.statement.Statement;
035    import net.sf.jsqlparser.statement.create.table.CreateTable;
036    import net.sf.jsqlparser.statement.delete.Delete;
037    import net.sf.jsqlparser.statement.drop.Drop;
038    import net.sf.jsqlparser.statement.insert.Insert;
039    import net.sf.jsqlparser.statement.replace.Replace;
040    import net.sf.jsqlparser.statement.select.Select;
041    import net.sf.jsqlparser.statement.select.SelectBody;
042    import net.sf.jsqlparser.statement.truncate.Truncate;
043    import net.sf.jsqlparser.statement.update.Update;
044    import net.sf.jsqlparser.test.tablesfinder.TablesNamesFinder;
045    
046    /**
047     * @author Brian Wing Shun Chan
048     * @author Raymond Augé
049     */
050    public class SQLChecker extends BaseChecker {
051    
052            public void afterPropertiesSet() {
053                    initTableNames();
054            }
055    
056            public void checkPermission(Permission permission) {
057                    throw new UnsupportedOperationException();
058            }
059    
060            @Override
061            public AuthorizationProperty generateAuthorizationProperty(
062                    Object... arguments) {
063    
064                    if ((arguments == null) || (arguments.length != 1) ||
065                            !(arguments[0] instanceof String)) {
066    
067                            return null;
068                    }
069    
070                    String sql = (String)arguments[0];
071    
072                    Statement statement = null;
073    
074                    try {
075                            statement = _jSqlParser.parse(new StringReader(sql));
076                    }
077                    catch (Exception e) {
078                            _log.error("Unable to parse SQL " + sql);
079    
080                            return null;
081                    }
082    
083                    String key = null;
084                    String value = null;
085    
086                    if (statement instanceof CreateTable) {
087                            key = "security-manager-sql-tables-create";
088    
089                            CreateTable createTable = (CreateTable)statement;
090    
091                            Table table = createTable.getTable();
092    
093                            value = table.getName();
094                    }
095                    else if (statement instanceof Delete) {
096                            key = "security-manager-sql-tables-delete";
097    
098                            Delete delete = (Delete)statement;
099    
100                            Table table = delete.getTable();
101    
102                            value = table.getName();
103                    }
104                    else if (statement instanceof Drop) {
105                            key = "security-manager-sql-tables-drop";
106    
107                            Drop drop = (Drop)statement;
108    
109                            value = drop.getName();
110                    }
111                    else if (statement instanceof Insert) {
112                            key = "security-manager-sql-tables-insert";
113    
114                            Insert insert = (Insert)statement;
115    
116                            Table table = insert.getTable();
117    
118                            value = table.getName();
119                    }
120                    else if (statement instanceof Replace) {
121                            key = "security-manager-sql-tables-replace";
122    
123                            Replace replace = (Replace)statement;
124    
125                            Table table = replace.getTable();
126    
127                            value = table.getName();
128                    }
129                    else if (statement instanceof Select) {
130                            key = "security-manager-sql-tables-select";
131    
132                            TableNamesFinder tableNamesFinder = new TableNamesFinder();
133    
134                            Select select = (Select)statement;
135    
136                            List<String> tableNames = tableNamesFinder.getTableNames(select);
137    
138                            value = StringUtil.merge(tableNames);
139                    }
140                    else if (statement instanceof Truncate) {
141                            key = "security-manager-sql-tables-truncate";
142    
143                            Truncate truncate = (Truncate)statement;
144    
145                            Table table = truncate.getTable();
146    
147                            value = table.getName();
148                    }
149                    else if (statement instanceof Update) {
150                            key = "security-manager-sql-tables-update";
151    
152                            Update update = (Update)statement;
153    
154                            Table table = update.getTable();
155    
156                            value = table.getName();
157                    }
158                    else {
159                            return null;
160                    }
161    
162                    AuthorizationProperty authorizationProperty =
163                            new AuthorizationProperty();
164    
165                    authorizationProperty.setKey(key);
166                    authorizationProperty.setValue(value);
167    
168                    return authorizationProperty;
169            }
170    
171            public boolean hasSQL(String sql) {
172                    Statement statement = null;
173    
174                    try {
175                            statement = _jSqlParser.parse(new StringReader(sql));
176                    }
177                    catch (Exception e) {
178                            _log.error("Unable to parse SQL " + sql);
179    
180                            return false;
181                    }
182    
183                    if (statement instanceof CreateTable) {
184                            CreateTable createTable = (CreateTable)statement;
185    
186                            return hasSQL(createTable);
187                    }
188                    else if (statement instanceof Select) {
189                            Select select = (Select)statement;
190    
191                            return hasSQL(select);
192                    }
193                    else if (statement instanceof Delete) {
194                            Delete delete = (Delete)statement;
195    
196                            return hasSQL(delete);
197                    }
198                    else if (statement instanceof Drop) {
199                            Drop drop = (Drop)statement;
200    
201                            return hasSQL(drop);
202                    }
203                    else if (statement instanceof Insert) {
204                            Insert insert = (Insert)statement;
205    
206                            return hasSQL(insert);
207                    }
208                    else if (statement instanceof Replace) {
209                            Replace replace = (Replace)statement;
210    
211                            return hasSQL(replace);
212                    }
213                    else if (statement instanceof Select) {
214                            Select select = (Select)statement;
215    
216                            return hasSQL(select);
217                    }
218                    else if (statement instanceof Truncate) {
219                            Truncate truncate = (Truncate)statement;
220    
221                            return hasSQL(truncate);
222                    }
223                    else if (statement instanceof Update) {
224                            Update update = (Update)statement;
225    
226                            return hasSQL(update);
227                    }
228    
229                    return false;
230            }
231    
232            protected boolean hasSQL(CreateTable createTable) {
233                    return isAllowedTable(createTable.getTable(), _createTableNames);
234            }
235    
236            protected boolean hasSQL(Delete delete) {
237                    TableNamesFinder tableNamesFinder = new TableNamesFinder();
238    
239                    List<String> tableNames = tableNamesFinder.getTableNames(delete);
240    
241                    return isAllowedTables(tableNames, _deleteTableNames);
242            }
243    
244            protected boolean hasSQL(Drop drop) {
245                    return isAllowedTable(drop.getName(), _dropTableNames);
246            }
247    
248            protected boolean hasSQL(Insert insert) {
249                    TableNamesFinder tableNamesFinder = new TableNamesFinder();
250    
251                    List<String> tableNames = tableNamesFinder.getTableNames(insert);
252    
253                    return isAllowedTables(tableNames, _insertTableNames);
254            }
255    
256            protected boolean hasSQL(Replace replace) {
257                    TableNamesFinder tableNamesFinder = new TableNamesFinder();
258    
259                    List<String> tableNames = tableNamesFinder.getTableNames(replace);
260    
261                    return isAllowedTables(tableNames, _replaceTableNames);
262            }
263    
264            protected boolean hasSQL(Select select) {
265                    TableNamesFinder tableNamesFinder = new TableNamesFinder();
266    
267                    List<String> tableNames = tableNamesFinder.getTableNames(select);
268    
269                    return isAllowedTables(tableNames, _selectTableNames);
270            }
271    
272            protected boolean hasSQL(Truncate truncate) {
273                    return isAllowedTable(truncate.getTable(), _truncateTableNames);
274            }
275    
276            protected boolean hasSQL(Update update) {
277                    TableNamesFinder tableNamesFinder = new TableNamesFinder();
278    
279                    List<String> tableNames = tableNamesFinder.getTableNames(update);
280    
281                    return isAllowedTables(tableNames, _updateTableNames);
282            }
283    
284            protected void initTableNames() {
285                    _allTableNames = getPropertySet("security-manager-sql-tables-all");
286                    _createTableNames = getPropertySet(
287                            "security-manager-sql-tables-create");
288                    _deleteTableNames = getPropertySet(
289                            "security-manager-sql-tables-delete");
290                    _dropTableNames = getPropertySet("security-manager-sql-tables-drop");
291                    _insertTableNames = getPropertySet(
292                            "security-manager-sql-tables-insert");
293                    _replaceTableNames = getPropertySet(
294                            "security-manager-sql-tables-replace");
295                    _selectTableNames = getPropertySet(
296                            "security-manager-sql-tables-select");
297                    _truncateTableNames = getPropertySet(
298                            "security-manager-sql-tables-truncate");
299                    _updateTableNames = getPropertySet(
300                            "security-manager-sql-tables-update");
301            }
302    
303            protected boolean isAllowedTable(
304                    String tableName, Set<String> allowedTableNames) {
305    
306                    if (_allTableNames.contains(tableName) ||
307                            allowedTableNames.contains(tableName)) {
308    
309                            return true;
310                    }
311    
312                    return false;
313            }
314    
315            protected boolean isAllowedTable(
316                    Table table, Set<String> allowedTableNames) {
317    
318                    String tableName = table.getName();
319    
320                    return isAllowedTable(tableName, allowedTableNames);
321            }
322    
323            protected boolean isAllowedTables(
324                    List<String> tableNames, Set<String> allowedTableNames) {
325    
326                    for (String tableName : tableNames) {
327                            if (!isAllowedTable(tableName, allowedTableNames)) {
328                                    return false;
329                            }
330                    }
331    
332                    return true;
333            }
334    
335            private static Log _log = LogFactoryUtil.getLog(SQLChecker.class);
336    
337            private Set<String> _allTableNames;
338            private Set<String> _createTableNames;
339            private Set<String> _deleteTableNames;
340            private Set<String> _dropTableNames;
341            private Set<String> _insertTableNames;
342            private JSqlParser _jSqlParser = new CCJSqlParserManager();
343            private Set<String> _replaceTableNames;
344            private Set<String> _selectTableNames;
345            private Set<String> _truncateTableNames;
346            private Set<String> _updateTableNames;
347    
348            private class TableNamesFinder extends TablesNamesFinder {
349    
350                    public TableNamesFinder() {
351                            tables = new ArrayList<String>();
352                    }
353    
354                    public List<String> getTableNames(Delete delete) {
355                            Table table = delete.getTable();
356    
357                            tables.add(table.getName());
358    
359                            Expression where = delete.getWhere();
360    
361                            where.accept(this);
362    
363                            return tables;
364                    }
365    
366                    public List<String> getTableNames(Insert insert) {
367                            Table table = insert.getTable();
368    
369                            tables.add(table.getName());
370    
371                            ItemsList itemsList = insert.getItemsList();
372    
373                            itemsList.accept(this);
374    
375                            return tables;
376                    }
377    
378                    public List<String> getTableNames(Replace replace) {
379                            Table table = replace.getTable();
380    
381                            tables.add(table.getName());
382    
383                            ItemsList itemsList = replace.getItemsList();
384    
385                            itemsList.accept(this);
386    
387                            return tables;
388                    }
389    
390                    public List<String> getTableNames(Select select) {
391                            SelectBody selectBody = select.getSelectBody();
392    
393                            selectBody.accept(this);
394    
395                            return tables;
396                    }
397    
398                    public List<String> getTableNames(Update update) {
399                            Table table = update.getTable();
400    
401                            tables.add(table.getName());
402    
403                            Expression where = update.getWhere();
404    
405                            where.accept(this);
406    
407                            return tables;
408                    }
409    
410            }
411    
412    }