001// Copyright (c) 2001 Hursh Jain (http://www.mollypages.org) 
002// The Molly framework is freely distributable under the terms of an
003// MIT-style license. For details, see the molly pages web site at:
004// http://www.mollypages.org/. Use, modify, have fun !
005
006package fc.web.servlet;
007
008import javax.servlet.*;
009import javax.servlet.http.*;
010import java.sql.*;
011import java.io.*;
012import java.util.*;
013
014import fc.io.*;
015import fc.jdbc.*;
016import fc.web.*;
017
018/** 
019Raw SQL query servlet. Useful to test for and do manual SQL
020queries from a web page. Uses the {@link ConnectionMgr}
021currently installed in the {@link fc.web.WebApp}
022<br>
023XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 
024DOES NO AUTHENTICATION, for TESTING only. 
025EXTREMELY dangerous to your data if used by an attacker.
026XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXxXXXX
027<p>
028ONLY install/use this servlet in a DEVELOPMENT environment.
029Since this servlet can be invoked as <tt>
030/servlet/fc.web.RawSQLServlet </tt>, and since it's often
031desirable to keep the <tt>/servlet</tt> mapping, this
032servlet expects another servlet init parameter called
033<tt>enable</tt>. This servlet will only run if this
034parameter is present and it's value is the string
035<tt>true</tt>.
036
037@author hursh jain
038*/
039public class RawSQLServlet extends FCBaseServlet 
040{
041public void init(ServletConfig conf) throws ServletException 
042  {
043  super.init(conf);
044
045  String enable = WebUtil.getRequiredParam(this, "enable");
046  if (! enable.equalsIgnoreCase("true")) {
047    throw new UnavailableException("Cannot run RawSQLServlet because the 'enable' init paramter was not set to true");
048    }
049  }
050
051public void doGet(HttpServletRequest request, HttpServletResponse response) 
052throws ServletException, IOException
053  {
054  PrintWriter out = response.getWriter();
055  
056  try {
057    showSQLForm(request, response);
058    }
059  catch (SQLException ex) {
060    out.print("<B> SQL Error:</B>\n" + ex);
061    out.print("<pre>");
062    ex.printStackTrace(out);
063    out.print("</pre>");
064    throw new ServletException(ex);
065    }
066  }
067  
068public void doPost(HttpServletRequest request, HttpServletResponse response) 
069throws ServletException, IOException
070  {
071  response.setContentType("text/html");
072  PrintWriter out = response.getWriter();
073  Connection con = null;
074  try { 
075    String query = request.getParameter("sql");
076    if (query == null) {
077      showSQLForm(request, response);
078      return;
079      }
080        
081    if (query == null) {
082      out.println("<b>Error: malformed query, contact administrator</b>");
083      return;
084      }
085
086    long startTime = System.currentTimeMillis(); 
087    con = getConnection();
088    Statement stmt = con.createStatement();
089
090    out.println("<html><head>");
091
092    //style used by queryutil.printrsashtmltable    
093    out.println("<style type=\"text/css\">");
094    out.println(".QueryUtil_Table { padding: 2px; border: 1px solid #660000; }");
095    out.println(".QueryUtil_HeaderCell { border: 1px solid; background: red; }");
096    out.println(".QueryUtil_Cell { border: 1px solid; }");
097    out.println("</style>");
098
099    out.println("</head><body bgcolor='#FFFFFF'><center>");
100    out.print("<h2>Query</h2><blockquote><pre>");
101    out.print(query);
102    out.println("</pre></blockquote>");
103  
104    ResultSet rs = stmt.executeQuery(query);
105    long querytime = System.currentTimeMillis() - startTime;
106    
107    out.println("<p><h2>Results</h2>");
108
109    QueryUtil.printResultSetHTMLTable(rs, out, 
110      QueryUtil.ResultSetPrintDirection.HORIZONTAL);
111
112      out.println("<hr>");
113      out.print("Query time: ");
114      //out.println("<br>" + querytime);
115      out.println(formatTime(querytime));
116      out.println("<hr>");
117      
118      printConInfo(out, con);
119      
120      out.println("</body></html>");
121    } 
122  catch (SQLException ex) {
123    out.print("<B> SQL Error:</B>\n" + ex);
124    out.print("<pre>");
125    ex.printStackTrace(out);
126    out.print("</pre>");
127    throw new ServletException(ex);
128    }
129  finally {
130    QueryUtil.close(null, null, con);
131    }
132  }  //~doPost
133
134
135void showSQLForm(HttpServletRequest request, HttpServletResponse response) 
136throws ServletException, IOException, SQLException
137  {
138  //we need to calculate this on the fly because the servlet may
139  //be invoked via some deployment specific servlet mapping name
140  String actionURL = request.getServletPath();
141  
142  response.setContentType("text/html");
143  PrintWriter out = response.getWriter();
144  out.println("<html><head></head><body bgcolor='#FFFFFF'><center>");
145  out.println("<h1>Raw SQL Servlet</h1><hr>");
146  out.println("<h2>Connection Information</h2>");
147  printJDBCInfo(out);
148  out.println("<h2>Enter a SQL Query</h2><hr>");
149  out.print("<form method=post action=" + actionURL + ">");
150  out.print("<textarea rows=10 cols=50 name=sql>");
151  out.print("</textarea><br>");
152  out.print("<INPUT TYPE=submit name=submit value=submit></INPUT>");
153  out.print("</form></center></body></html>");
154  out.flush();
155  }
156
157void printJDBCInfo(PrintWriter out) throws SQLException 
158  {
159  Connection con = null;
160  
161  try {
162    con = getConnection();
163    out.println("<table border=1 bordercolor=#3f6c96 cellpadding=4 cellspacing=0>");
164  
165    //driver etc
166    out.println("<tr><td align=right>");
167    out.println("Driver ");
168    out.println("</td>");
169    out.println("<td align=left>");
170    out.println(WebApp.getInstance(appName).getConnectionMgr().getDriver());  
171    out.println("</td></tr>");  
172    
173    //catalog
174    out.println("<tr><td align=right>");
175    out.println("Catalog ");
176    out.println("</td>");
177    out.println("<td align=left>");
178    out.println(con.getCatalog());  
179    out.println("</td></tr>");  
180  
181    //autocommit
182    out.println("<tr><td align=right>");
183    out.println("Autocommit ");
184    out.println("</td>");
185    out.println("<td align=left>");
186    out.println(con.getAutoCommit()); 
187    out.println("</td></tr>");  
188  
189    //transaction-isolation
190    out.println("<tr><td align=right>");
191    out.println("Transaction isolation ");
192    out.println("</td>");
193    out.println("<td align=left>");
194    out.println(con.getTransactionIsolation()); 
195    out.println("</td></tr>");  
196    
197    out.println("</table>");
198    }
199  finally {
200    QueryUtil.close(null, null, con);
201    } 
202  }
203
204
205void printConInfo(PrintWriter out, Connection con) throws SQLException 
206  {
207  out.println("<table border=0>");
208
209  SQLWarning warning = con.getWarnings();
210
211  //SQL Warnings
212  out.println("<tr><td align=right>");
213  out.println("SQL Warnings: ");
214  out.println("</td>");
215
216  out.println("<td align=left>");
217  if (warning == null) {
218    out.println("None");  
219    }
220  else {
221    out.println(warning);
222    while (warning.getNextWarning() != null) 
223      {
224      out.println("<br>");
225      out.println(warning);
226      }
227    }
228  out.println("</td></tr>");  
229  out.println("</table>");
230  }
231
232String formatTime(long time) 
233  {
234  if (time < 1000)
235    return time + " ms";
236  else
237    return (time % 1000) + " seconds";
238  }
239
240}