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