Source code

001/*
002 * File   : $Source: /alkacon/cvs/opencms-ocee/org.opencms.ocee.vfsdoctor/src/org/opencms/ocee/vfsdoctor/CmsVfsDoctorSqlConsole.java,v $
003 * Date   : $Date: 2010/09/06 13:38:58 $
004 * Version: $Revision: 1.21 $
005 *
006 * Copyright (c) 2005 Alkacon Software GmbH (http://www.alkacon.com)
007 * All rights reserved.
008 *
009 * This source code is the intellectual property of Alkacon Software GmbH.
010 * It is PROPRIETARY and CONFIDENTIAL.
011 * Use of this source code is subject to license terms.
012 *
013 * In order to use this source code, you need written permission from
014 * Alkacon Software GmbH. Redistribution of this source code, in modified
015 * or unmodified form, is not allowed unless written permission by
016 * Alkacon Software GmbH has been given.
017 *
018 * ALKACON SOFTWARE GMBH MAKES NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY
019 * OF THIS SOURCE CODE, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
020 * TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
021 * PURPOSE, OR NON-INFRINGEMENT. ALKACON SOFTWARE GMBH SHALL NOT BE LIABLE FOR ANY
022 * DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR DISTRIBUTING
023 * THIS SOURCE CODE OR ITS DERIVATIVES.
024 *
025 * For further information about Alkacon Software GmbH, please see the
026 * company website: http://www.alkacon.com
027 */
028
029package org.opencms.ui.apps.dbmanager.sqlconsole;
030
031import org.opencms.db.I_CmsVfsDriver;
032import org.opencms.db.generic.CmsSqlManager;
033import org.opencms.i18n.CmsMessageContainer;
034import org.opencms.main.CmsLog;
035import org.opencms.report.I_CmsReport;
036import org.opencms.security.CmsSecurityException;
037import org.opencms.ui.apps.Messages;
038
039import java.sql.Connection;
040import java.sql.PreparedStatement;
041import java.sql.ResultSet;
042import java.sql.ResultSetMetaData;
043import java.sql.SQLException;
044import java.util.ArrayList;
045import java.util.Arrays;
046import java.util.List;
047import java.util.stream.Collectors;
048
049import org.apache.commons.logging.Log;
050
051/**
052 * Manages the SQL console.<p>
053 *
054 * @author Michael Moossen
055 *
056 * @version $Revision: 1.21 $
057 *
058 * @since 6.0.0
059 */
060public final class CmsSqlConsoleExecutor {
061
062    /** The log object for this class. */
063    private static final Log LOG = CmsLog.getLog(CmsSqlConsoleExecutor.class);
064
065    /** SQL manager with specialized queries. */
066    private CmsSqlManager m_sqlManager;
067
068    /**
069     * Default Constructor.<p>
070     */
071    public CmsSqlConsoleExecutor() {
072
073        m_sqlManager = new CmsSqlManager();
074        m_sqlManager.init(I_CmsVfsDriver.DRIVER_TYPE_ID, "default");
075    }
076
077    /**
078     * Main method of this class. Executes the given sql query.<p>
079     *
080     * This method also checks the permissions for executing sql sentences,
081     * according to the <code>vfs-doctor.xml</code> configuration file.<p>
082     *
083     * @param sql the sql query to execute
084     * @param pool name of the db pool to use
085     * @param report the report to write the output
086     * @param errors a list to append errors to
087     *
088     * @return a <code>{@link List}</code> if the sql is a SELECT sentence, or <code>null</code>.
089     */
090    public CmsSqlConsoleResults execute(String sql, String pool, I_CmsReport report, List<Throwable> errors) {
091
092        try {
093            CmsMessageContainer message = Messages.get().container(Messages.RPT_SQLCONSOLE_BEGIN_0);
094            report.println(message, I_CmsReport.FORMAT_HEADLINE);
095            if (LOG.isInfoEnabled()) {
096                LOG.info(message.key());
097            }
098
099            List<String> sentences = normalize(sql);
100            if (sentences.size() < 1) {
101                write(
102                    report,
103                    Messages.get().getBundle(report.getLocale()).key(Messages.ERR_SQLCONSOLE_NOTHING_TO_EXECUTE_0));
104            } else {
105                for (String sentence : sentences) {
106                    if (!checkPermissions(sentence)) {
107                        writeError(
108                            report,
109                            new CmsSecurityException(
110                                Messages.get().container(
111                                    Messages.ERR_SQLCONSOLE_NO_PERMISSIONS_EXEC_SENTENCE_1,
112                                    sentence)));
113                    } else {
114                        if (!sentence.toUpperCase().startsWith("SELECT")
115                            && !sentence.toUpperCase().startsWith("SHOW")) {
116                            int res = executeUpdate(sentence, pool);
117                            message = Messages.get().container(
118                                Messages.RPT_SQLCONSOLE_ROWS_AFFECTED_1,
119                                Integer.valueOf(res));
120                            report.println(message);
121                            if (LOG.isInfoEnabled()) {
122                                LOG.info(message);
123                            }
124                        } else {
125                            CmsSqlConsoleResults res = executeQuery(sentence, pool);
126                            // writeTable(report, res);
127                            message = Messages.get().container(
128                                Messages.RPT_SQLCONSOLE_NUM_ROWS_RETRIEVED_1,
129                                Integer.valueOf(res.getData().size()));
130                            report.println(message);
131                            if (LOG.isInfoEnabled()) {
132                                LOG.info(message);
133                            }
134                            return res;
135                        }
136                    }
137                }
138            }
139        } catch (Throwable e) {
140            errors.add(e);
141        } finally {
142            CmsMessageContainer message = Messages.get().container(Messages.RPT_SQLCONSOLE_END_0);
143            report.println(message, I_CmsReport.FORMAT_HEADLINE);
144            if (LOG.isInfoEnabled()) {
145                LOG.info(message.key());
146            }
147        }
148        return null;
149    }
150
151    /**
152     * Checks the permission to be executed for a single sql sentence.<p>
153     * @param sentence the sentence to check for permission
154     *
155     * @return <code>true</code> if the sentence is allowed to be executed
156     */
157    private boolean checkPermissions(String sentence) {
158
159        sentence = sentence.toUpperCase();
160
161        // protected user from the case where they accidentally left off the condition in DELETE statements
162        // (you can still use dummy conditions like 'WHERE 1=1' if you really want to delete everything)
163        if (sentence.contains("DELETE FROM") && !sentence.contains("WHERE")) {
164            return false;
165        }
166        return true;
167    }
168
169    /**
170     * Executes a single <code>SELECT</code> sql sentence.<p>
171     *
172     * @param sentence the sentence to execute
173     * @param poolName the name of the pool to use
174     *
175     * @return the list of rows returned by the rdbms
176     *
177     * @throws SQLException in the case of a error
178     */
179    @SuppressWarnings("resource")
180    private CmsSqlConsoleResults executeQuery(String sentence, String poolName) throws SQLException {
181
182        Connection conn = null;
183        PreparedStatement stmt = null;
184        ResultSet res = null;
185
186        CmsSqlManager sqlManager = m_sqlManager;
187
188        try {
189            conn = sqlManager.getConnection(poolName);
190            stmt = sqlManager.getPreparedStatementForSql(conn, sentence);
191            res = stmt.executeQuery();
192
193            // add headings
194            ResultSetMetaData metadata = res.getMetaData();
195            List<String> heading = new ArrayList<>();
196            for (int i = 0; i < metadata.getColumnCount(); i++) {
197                heading.add(metadata.getColumnName(i + 1));
198            }
199            List<List<Object>> data = new ArrayList<List<Object>>();
200
201            // add contents
202            while (res.next()) {
203                List<Object> row = new ArrayList<Object>();
204                for (int i = 0; i < metadata.getColumnCount(); i++) {
205                    Object value = res.getObject(i + 1);
206                    if ((value instanceof String)
207                        || (value instanceof Integer)
208                        || (value instanceof Long)
209                        || (value instanceof Float)
210                        || (value instanceof Double)) {
211                        row.add(value);
212                    } else if (value == null) {
213                        row.add(null);
214                    } else {
215                        row.add(String.valueOf(value));
216                    }
217                }
218                data.add(row);
219            }
220            return new CmsSqlConsoleResults(heading, data);
221        } finally {
222            sqlManager.closeAll(null, conn, stmt, res);
223        }
224    }
225
226    /**
227     * Executes a single sql sentence.<p>
228     *
229     * For <code>SELECT</code> sentences use <code>{@link #executeQuery(String, String)}</code>.<p>
230     *
231     * @param sentence the sentence to execute
232     * @param poolName the name of the pool to use
233     *
234     * @return the number of affected rows
235     *
236     * @throws SQLException if there is an error
237     */
238    private int executeUpdate(String sentence, String poolName) throws SQLException {
239
240        Connection conn = null;
241        PreparedStatement stmt = null;
242        int ret = 0;
243
244        CmsSqlManager sqlManager = m_sqlManager;
245        try {
246            conn = sqlManager.getConnection(poolName);
247            stmt = sqlManager.getPreparedStatementForSql(conn, sentence);
248            ret = stmt.executeUpdate();
249        } finally {
250            sqlManager.closeAll(null, conn, stmt, null);
251        }
252        return ret;
253    }
254
255    /**
256     * Returns a list of single sql sentences,
257     * removing all special chars like tabs or eol's.<p>
258     *
259     * @param sql the sql query
260     *
261     * @return a list of sql sentences
262     */
263    private List<String> normalize(String sql) {
264
265        String normSql = sql.replaceAll(";", "; ");
266        normSql = normSql.replaceAll("\\s+", " ");
267        return Arrays.stream(normSql.split(";")).map(s -> s.trim()).collect(Collectors.toList());
268    }
269
270    /**
271     * Writes the given string to the report,
272     * and, if enabled, to the opencms log file.<p>
273     *
274     * @param report the report
275     * @param string the string
276     */
277    private void write(I_CmsReport report, String string) {
278
279        report.println(
280            org.opencms.report.Messages.get().container(
281                org.opencms.report.Messages.RPT_ARGUMENT_1,
282                string.replaceAll("'", "\\\\'")));
283        if (LOG.isInfoEnabled()) {
284            LOG.info("SQLConsole:" + string);
285        }
286    }
287
288    /**
289     * Writes the given error to the report,
290     * and, if enabled, to the opencms log file.<p>
291     *
292     * @param report the report to write to
293     * @param e the exception
294     */
295    private void writeError(I_CmsReport report, Throwable e) {
296
297        report.println(e);
298        if (LOG.isWarnEnabled()) {
299            LOG.warn("SQLConsole", e);
300        }
301    }
302}