001
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
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 }