Untitled

/*
 * Created on 19 mrt 2010
 */

package craterstudio.indiespot.http;

import java.io.ByteArrayOutputStream;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import craterstudio.sql.basic.DataStoreException;
import craterstudio.text.TextValues;
import craterstudio.util.ArrayMap;
import jawnae.net.http.core.HttpRequest;
import jawnae.net.http.core.HttpResponse;
import jawnae.net.http.core.HttpService;
import jawnae.net.http.core.StatusCode;

public class SqlInjectionService extends HttpService
{
   @Override
   public void serveGET(HttpRequest request, HttpResponse response) throws Exception
   {
      this.servePOST(request, response);
   }

   @Override
   public void servePOST(HttpRequest request, HttpResponse response) throws Exception
   {
      try
      {
         ByteArrayOutputStream baos = new ByteArrayOutputStream();
         PrintStream out = new PrintStream(baos);
         this.serve(request, out);
         byte[] data = baos.toByteArray();

         response.setStatusCode(StatusCode.OK);
         response.setKeepAlive(true);
         response.setContentEncodingGzip(false);
         response.setContentType("text/html");
         response.setTransferEncodingChunked(false);
         response.setContent(data);
      }
      catch (Exception exc)
      {
         exc.printStackTrace();

         response.setStatusCode(StatusCode.INTERNAL_SERVER_ERROR);
         response.setKeepAlive(true);
         response.setContentEncodingGzip(false);
         response.setContentType("text/plain");
         response.setTransferEncodingChunked(true);

         PrintStream out = response.createPrintStream();
         exc.printStackTrace(out);
         out.close();
      }
   }

   private void serve(HttpRequest request, PrintStream out) throws Exception
   {
      out.println("<html>\r\n");
      out.println("   <head>\r\n");
      out.println("      <title>Boooo!</title>\r\n");
      out.println("   </head>\r\n");
      out.println("   <body>\r\n");
      out.println("      <h1>Boo</h1>\r\n");

      //      final String memberid = request.getPost("memberid");

      final List<Map<String, Object>> records;

      String minid;
      String maxid;

      Connection sql = null;
      try
      {
         System.out.println("opening sql connection");
         sql = createMySQLConnection();

         Map<String, Object> row = this.querySelectOneRow(sql, "SELECT MIN(memberid) AS 'minid', MAX(memberid) AS 'maxid' FROM member");
         minid = String.valueOf(row.get("minid"));
         maxid = String.valueOf(row.get("maxid"));

         if (request.isRegularPost())
         {
            if (request.getPost("minid") != null)
               minid = request.getPost("minid");
            if (request.getPost("maxid") != null)
               maxid = request.getPost("maxid");
         }

         records = this.querySelect(sql, "SELECT memberid,username,emailaddress FROM member WHERE memberid BETWEEN " + minid + " AND " + maxid + " AND is_hidden = 0");

         sql.close();
      }
      catch (SQLException exc)
      {
         throw new IllegalStateException(exc);
      }
      finally
      {
         System.out.println("closing sql connection");

         if (sql != null)
         {
            try
            {
               sql.close();
            }
            catch (SQLException exc)
            {
               throw new IllegalStateException(exc);
            }
         }
      }

      out.println("      <h2>Filter</h2>\r\n");
      out.println("      <form method='post' action='/app/sql-injection'>\r\n");
      out.println("         <fieldset>\r\n");
      out.println("            <legend>Specify your ID range:</legend>\r\n");
      out.println("            Lowest member ID: <input type='text' name='minid'/ value='" + minid + "'><br>\r\n");
      out.println("            Highest member ID: <input type='text' name='maxid' value='" + maxid + "'/><br>\r\n");
      out.println("            <input type='submit' name='ok' value='filter on range'><br>\r\n");
      out.println("         </fieldset>\r\n");
      out.println("      </form>\r\n");

      out.println("      <h2>Results</h2>\r\n");
      out.println("      <table border=1>");
      for (Map<String, Object> record : records)
      {
         out.println("         <tr>");
         for (Entry<String, Object> entry : record.entrySet())
            out.println("            <td>" + entry.getValue() + "</td>");
         out.println("         </tr>");
      }
      out.println("      </table>");

      out.println("   </body>\r\n");
      out.println("</html>\r\n");
   }

   private final Connection createMySQLConnection() throws SQLException
   {
      try
      {
         Class.forName("com.mysql.jdbc.Driver");
      }
      catch (Exception exc)
      {
         throw new DataStoreException("MySQL driver not found");
      }

      String host = "localhost";
      int port = 3306;
      String user = "indiespot_shivan";
      String pass = "navihs";
      String dbname = "indiespot_shivan";

      String url = "jdbc:mysql://" + host + ":" + (port == 0 ? 3306 : port) + "/";
      Connection con = DriverManager.getConnection(url, user, pass);

      Statement stmt = con.createStatement();
      stmt.execute("USE " + dbname);
      stmt.close();

      return con;
   }

   private final Map<String, Object> querySelectOneRow(Connection sql, String query) throws SQLException
   {
      List<Map<String, Object>> records = querySelect(sql, query);
      if (records.isEmpty())
         return null;
      if (records.size() > 1)
         throw new IllegalStateException("too many rows: " + records.size());
      return records.get(0);
   }

   private final List<Map<String, Object>> querySelect(Connection sql, String query) throws SQLException
   {
      System.out.println("executing query: " + query);
      Statement stmt = sql.createStatement();
      ResultSet resultset = stmt.executeQuery(query);
      List<Map<String, Object>> records = this.grabResultSet(resultset);
      stmt.close();
      return records;
   }

   private final List<Map<String, Object>> grabResultSet(ResultSet resultset) throws SQLException
   {
      final ResultSetMetaData meta = resultset.getMetaData();
      final int columnCount = meta.getColumnCount();

      final String[] columnNames = new String[columnCount];
      final int[] columnTypes = new int[columnCount];

      for (int i = 0; i < columnCount; i++)
      {
         columnNames[i] = meta.getColumnName(i + 1);
         columnTypes[i] = meta.getColumnType(i + 1);

         System.out.println("columnNames[" + i + "] => " + columnNames[i]);
      }

      final List<Map<String, Object>> records = new ArrayList<Map<String, Object>>();

      while (resultset.next())
      {
         final Map<String, Object> record = new ArrayMap<String, Object>();

         for (int i = 0; i < columnCount; i++)
         {
            final Object value;

            switch (columnTypes[i])
            {
               case Types.TINYINT:
               case Types.SMALLINT:
               case Types.INTEGER:
               case Types.BIGINT:
                  value = Integer.valueOf(resultset.getInt(i + 1));
                  break;

               case Types.CHAR:
               case Types.VARCHAR:
               case Types.LONGVARCHAR:
                  value = resultset.getString(i + 1);
                  break;

               case Types.TIMESTAMP:
                  value = Long.valueOf(resultset.getLong(i + 1));
                  break;

               case Types.BINARY:
               case Types.VARBINARY:
               case Types.LONGVARBINARY:
                  byte[] bytes = resultset.getBytes(i + 1);
                  value = "0x" + TextValues.hexRawEncode(bytes);
                  break;

               default:
                  throw new UnsupportedOperationException("type: " + columnTypes[i]);
            }

            record.put(columnNames[i], value);
         }

         records.add(record);
      }

      return records;
   }
}