001: package com.jamonapi.proxy;
002:
003: import java.util.*;
004: import com.jamonapi.utils.*;
005:
006: /** SQLDeArger takes a sql statement and 1) replaces argument values ('souza', "souza", 'souza''s', 100, 100.5, 0xff, 10e9) with question marks
007: * It makes full sql statements look more like a prepared statement. 2) Returns a sql type which is simply the first word of the command
008: * (typically 'select', 'update' etc. 3) Returns any specified keywords that are in the parsed sql. This is a good way to return table names.
009: *
010: * A normal sql statement with argument values would generate too much data for JAMon and wouldn't be very good for understanding how your query
011: * performed. Coneceptually the following queries are the same: 1) select * from table where name='steve', 2) select * from table where name='mindy'.
012: * However, if you passed both strings to jamon the 'sameness' wouldn't show up in the stats as each is a different string. However by putting
013: * question marks in place of the values this problem can be resolved (i.e. select * from table where name=?). One issue with the way this is done
014: * at this point is numbers or strings in other places can be replaced too. This shouldn't affect monitoring however. For example
015: * This "select abs(200) from table", would be parsed to "select abs(?) from table".
016: *
017: * However, numbers of the format 100.00, really are multiple tokens. And will appear in the returned
018: * strings as ?.?.
019:
020: *
021: * The class name SQLDeArger refers to the fact that argument values are removed from SQL statements.
022: *
023: * This class is also useful for logging sql statements.
024: * @author steve souza
025: *
026: */
027:
028: public class SQLDeArger {
029:
030: private int parseSize;// size of the sql statement to parse
031: private StringBuffer parsedSQL = new StringBuffer();// where the resulting sql statement will reside
032: private char[] sqlChars;// characters in sql string
033: private String sqlToParse;// original sql
034: private int delimCounter = 0;//character counter
035: private int currentCharNum = 0;// current character in sql that is being proceesed
036: private boolean isInString = false;// indicates whether or not the parser is currently in a string i.e. 'steve'
037: private char delim; // string delimeter which will be either ' or "
038: private int totalDelims;// a counter of how many times delimeter appears in a string.
039: private boolean firstToken = true;// the first token processed. usually will be select, update, insert etc.
040: private String sqlType;// the first token such as select, update, delete, insert
041: private List matchStrings;// a list of strings to match in the sql statement. table names are a good example
042: private String[] matches;// the list of returned matches.
043:
044: private static Map sqlTypes = AppMap.createInstance();// case insensitive map.
045: static {
046: // These values are just checked for existance so the value can be null. These indicate the type of sql statement.
047: sqlTypes.put("select", "select");
048: sqlTypes.put("update", "update");
049: sqlTypes.put("delete", "delete");
050: sqlTypes.put("insert", "insert");
051: sqlTypes.put("truncate", "truncate");
052:
053: sqlTypes.put("exec", "exec");
054: sqlTypes.put("create", "create");
055: sqlTypes.put("drop", "drop");
056: sqlTypes.put("alter", "alter");
057:
058: sqlTypes.put("commit", "commit");
059: sqlTypes.put("rollback", "rollback");
060: sqlTypes.put("save", "save");
061:
062: sqlTypes.put("grant", "grant");
063: sqlTypes.put("revoke", "revoke");
064:
065: }
066:
067: /** Accepts string to parse */
068: public SQLDeArger(String sql) {
069: this (sql, null);
070:
071: }
072:
073: /** Accepts strings to parse and a List of strings to check to see if they are in the sql statement. A good use for this is to
074: * pass table names into the constructor. After the constructor is called the sql will already have been parsed
075: * @param sql
076: * @param matchStrings
077: */
078: public SQLDeArger(String sql, List matchStrings) {
079: this .sqlToParse = sql.trim() + " ";// ensures that the last character is always a space and not part of the query.
080: parseSize = sqlToParse.length() - 1; // exclude space which lets me not have to worry about next char.
081: sqlChars = sqlToParse.toCharArray();// characters that will be parsed
082: setMatchStrings(matchStrings);
083: parse();
084: }
085:
086: /** Parse the passed in where clause and break it along token lines. */
087: private SQLDeArger parse() {
088:
089: // tokens are strings broken on word boundaries like spaces
090: while (hasTokens()) {
091: String token = getNextToken();
092: if (firstToken) {
093: setSQLType(token);
094: firstToken = false;
095: }
096:
097: // if the token is a number such as 100, 100.5 0xff, 10E9 or a quoted string such as 'steve'
098: // the replace it with a '?'
099: token = (isFloatString(token) || isQuotedString(token)) ? "?"
100: : token;
101: parsedSQL.append(token);
102: }
103:
104: parseMatches();
105:
106: return this ;
107: }
108:
109: /** Return sql with original argument values replaced with '?'. For example: select * from table where name=? */
110: public String getParsedSQL() {
111: return parsedSQL.toString();
112: }
113:
114: /**
115: * Get sql that was passed in to parse.
116: */
117: public String getSQLToParse() {
118: return sqlToParse;
119: }
120:
121: /** Return the first word from the sql command. These would include: select, update, delete, create, insert, commit,...
122: * If the word is not recognized then 'other' is returned.
123: */
124: public String getSQLType() {
125: return sqlType;
126: }
127:
128: /** Returns an array of Strings that matched the Strings specified in
129: * the matches arraylist. Note that the matches are performed after arg
130: * values have been replaced on the sql with '?'.
131: */
132: public String[] getMatches() {
133: return matches;
134: }
135:
136: /** Returns true if there were any matches against the match Strings */
137: public boolean hasMatches() {
138: return (matches == null || matches.length == 0) ? false : true;
139: }
140:
141: /** Returns the number of matches or 0 if there were none */
142: public int getNumMatches() {
143: return (hasMatches()) ? matches.length : 0;
144: }
145:
146: /** One for the statement, one for the keyword type of the statment, and the other numbers are for the matches */
147: int getNumAll() {
148: return 3 + getNumMatches();
149: }
150:
151: /** Return an array that has 1) all sql, 2) the sql type, 3) the parsed sql, 4) any matched strings if they exist. The array will be at least
152: * 2 long. This is useful to pass all the strings in the array to jamon to track stats associated with the query.
153: */
154: String[][] getAll() {
155:
156: int size = getNumAll(); // 2 represents the sql and sqltype values, so that is always there.
157: String[][] allData = new String[size][];
158:
159: // start at position 2 assigning any matches to the array.
160: for (int i = 0, j = 0; i < size; i++) {
161: allData[i] = new String[3];// ms., and value
162: allData[i][1] = getSQLToParse();
163: allData[i][2] = "ms.";
164: // note this loop matches one in SQLDeArgMon constructor and positions are important
165: // The constructor must be changed if this method changes - kind of ugly...
166: if (i == 0) // All
167: allData[i][0] = "All";
168: else if (i == 1) // SQL Type
169: allData[i][0] = getSQLType();
170: else if (i == 2) // parsed SQL
171: allData[i][0] = getParsedSQL();
172: else
173: // Matches in sql such as table names.
174: allData[i][0] = matches[j++];
175: }
176:
177: return allData;
178: }
179:
180: /** Add string to see if it matches in the query */
181: public void addMatchString(String matchString) {
182: matchStrings.add(matchString);
183:
184: }
185:
186: /** Determine if the matches strings are in the parsed sql */
187: private void parseMatches() {
188:
189: if (matchStrings != null) {
190: String sql = getParsedSQL();
191: List matchesList = new ArrayList();
192:
193: Iterator iter = matchStrings.iterator();
194: while (iter.hasNext()) {
195: Object matchObj = iter.next(); // passed in matches such as table names
196: String matchStr = (matchObj == null) ? null : matchObj
197: .toString();
198: if (sql.indexOf(matchStr) > 0)// i.e. a match found
199: matchesList.add(matchStr);
200:
201: }
202:
203: matches = (String[]) matchesList.toArray(new String[0]);// convert matches into an array
204: }
205:
206: }
207:
208: /** Note matchStrings should contain Strings. If it doesn't toString() will be called on the objects */
209: void setMatchStrings(List matchStrings) {
210: this .matchStrings = matchStrings;
211: }
212:
213: /** SQL types are the first word that is in a sql statement. Examples are
214: * insert, delete, update, and select. However, any word that you add by calling this
215: * method will be detected as a sql type. Note the JDBCMonProxy uses this info
216: * to add a monitor for whenever a select, insert etc are executed. This gives the number
217: * of times and performances of the sql types. A list of all the default
218: * sql types follows: select, update, delete, insert, truncate, exec, create, drop, alter
219: * commit, rollback, grant, revoke, save. Any value that isn't on the list will return
220: * 'other'. The getSQLType method returns the SQL type value in the sql statement passed to the
221: * constructor.
222: * *
223: * @param type
224: */
225: public static void putSQLType(String type) {
226: sqlTypes.put(type, type);
227: }
228:
229: private void setSQLType(String type) {
230: sqlType = (String) sqlTypes.get(type);
231: if (sqlType == null)
232: sqlType = "other";
233:
234: }
235:
236: // returns true as long as there are characters to process
237: private boolean hasTokens() {
238: return (currentCharNum < parseSize) ? true : false;
239: }
240:
241: // Returns String tokens such as: select, from, where, table, etc.
242: private String getNextToken() {
243:
244: int start = currentCharNum;
245: int end = 0;
246:
247: // loop until word boundary
248: while (end == 0 && currentCharNum < parseSize) {
249: setStringDelim();
250:
251: // an example of a word boundary would be next char of space while not in a string, or a puctuation mark
252: if (isWordBoundary(getCurrentChar(), getNextChar()))
253: end = currentCharNum + 1;
254:
255: currentCharNum++;
256: }
257:
258: // return the word as a token if one was found
259: if (end > 0)
260: return sqlToParse.substring(start, end);
261: else
262: return null;
263:
264: }
265:
266: // determine if the character is punctuation or not.
267: private boolean isPunctuation(char ch) {
268: return (!isInString() && !Character.isLetterOrDigit(ch));
269: }
270:
271: private boolean isWordBoundary(char currentCh, char nextCh) {
272: // word boundaries are special puncutation, when not in a string.
273: // select * from table where key=100
274: // first part of conditional would be triggered with convert(200. if currentchar was '('
275: // 2nd part: select *, convert(char(20)... from. if characther is 't' of select or 't'
276: // of 'convert'
277: return (isPunctuation(currentCh) && Character
278: .isLetterOrDigit(nextCh))
279: || (!isInString() && (Character.isWhitespace(nextCh) || !Character
280: .isLetterOrDigit(nextCh)));
281: }
282:
283: // Indicates if the character is embedded in a where clause string surrounded by ' or ".
284: // i.e. this would return true for any characters in between double quotes "steve's"
285: private boolean isInString() {
286: return isInString;
287: }
288:
289: private void setIsInString(boolean isInString) {
290: this .isInString = isInString;
291: }
292:
293: // can either be ' or "
294: private void setStringDelim(char delim) {
295: this .delim = delim;
296: }
297:
298: private char getStringDelim() {
299: return delim;
300: }
301:
302: // Get the current character of processing
303: private char getCurrentChar() {
304: return sqlChars[currentCharNum];
305: }
306:
307: // get next charater of processing.
308: private char getNextChar() {
309: return sqlChars[currentCharNum + 1];
310: }
311:
312: // Determine if the delimeter is " or '
313: private void setStringDelim() {
314: // if first delimiter in a string like 'steve' then prepare for a string to be
315: // processed
316:
317: // if in the string count delimeters to know when to end.
318: boolean isDelim = (isInString() && getCurrentChar() == getStringDelim());
319: if (isDelim)
320: totalDelims++;
321:
322: if (!isInString()
323: && (getCurrentChar() == '\'' || getCurrentChar() == '"')) {
324: delimCounter++;
325: totalDelims++;
326: } // else it is the last ' or " in 'steve' , 'mindy''s'''
327: else if (isInString() && getCurrentChar() == getStringDelim()
328: && getNextChar() != getStringDelim()
329: && totalDelims % 2 == 0) {
330: delimCounter--;
331: }
332:
333: if (!isInString() && delimCounter == 1) {
334: setStringDelim(getCurrentChar());
335: setIsInString(true);
336: } else if (isInString() && delimCounter == 0)
337: setIsInString(false);
338:
339: }
340:
341: // Any token that starts with a 0 returns true. i.e. 1000 0xFF, 10E9, would return true.
342: private boolean isFloatString(String str) {
343: // if null string or the first character is not a digit then this is not a number
344: if (str == null || !Character.isDigit(str.charAt(0)))
345: return false;
346: else
347: // else it is a number
348: return true;
349: }
350:
351: private boolean isQuotedString(String str) {
352: if (str == null || "".equals(str.trim()))
353: return false;
354:
355: char first = str.charAt(0);
356: char last = str.charAt(str.length() - 1);
357:
358: // either 'souza' or "souza" is a good quoted string.
359: if ((first == '\'' || first == '"') && first == last)
360: return true;
361: else
362: return false;
363: }
364:
365: private static void printDebugInfo(String sql) {
366: List matches = new ArrayList();
367: matches.add("employees");
368: matches.add("dependents");
369: matches.add("orders");
370: SQLDeArger s = new SQLDeArger(sql, matches);
371: System.out.println("\nSQL=" + sql);
372: System.out.println("ParsedSQL=" + s.getParsedSQL());
373: System.out.println("SQLType=" + s.getSQLType());
374: if (s.hasMatches()) {
375: System.out.println("String Matches=");
376: String[][] m = s.getAll();
377: for (int i = 0; i < m.length; i++) {
378: System.out.println("all array[" + i + "]=" + m[i][0]);
379: System.out.println("all array original [" + i + "]="
380: + m[i][1]);
381:
382: }
383: }
384: }
385:
386: /** Method that has test code for this class. Click 'View Code' above to view the code */
387: public static void main(String[] args) {
388: printDebugInfo("select col0, 'mindy', 'mindy''s', 'mindy''s''''', col2, convert('steve',100, 0xff, 100.0),* from table where salary>=100000 or salary<=200000 or name like 'steve%' and name in ('mindy','steve') and short= 20 or sand='no sand' or short=30 and sand='no sand' and sand='no sand' or salary in (select max(salary/2),100 from emps group by name having count(*)>5)");
389: printDebugInfo("select ?,*,? from table where name like ?");
390: printDebugInfo("select 'mindy', \"mindy\" from table where name like 'mindy'");
391: printDebugInfo(" delete from table where key name like 'mindy%' ");
392: printDebugInfo("myproc");
393: printDebugInfo("exec myproc");
394: printDebugInfo("select * from employees as e, customers as c, dependents as d where e.id=c.id and c.id=d.id and e.name in (select * from favorite where name like 'j%' and salary > 50000)");
395: printDebugInfo("select * from employees as e, customers as c, dependents as d where e.id=c.id and c.id=d.id and e.name not in ('steve','souza','jeff','beck') and salary in (100000,20000, 50000) and age!=50");
396: printDebugInfo("select 10_name_10, name_10_name,* from employees where age<50 and age!=10.0 and age!=-50.0 and age < 0xFF and age < 0x0123456789aAbBcCdDeEfF and age<+10E09");
397: // valid java variables can begin with a letter, $, or _
398: printDebugInfo("select 10/22/06, date102206, m10/d22/y06, 10name, $10_name, _10_name, 10_name_10, name_10_na1010me,* from employees where age<50 and age!=10.0 and age!=-50.0 and age < 0xFF and age < 0x0123456789aAbBcCdDeEfF and age<+10E09 and age<1.72E3F");
399: printDebugInfo("pageHits 'ssouza' jamon mb 100.5:pageHits jamon:pagehits.ssouza");
400: }
401:
402: }
|