Search code examples
phpsqlstringstrpos

Determine if a SQL query alters the database structure

I'm trying to find out if a string contains certain SQL commands that alter the database by:

  • creating new tables
  • deleting existing tables
  • creating new table columns
  • deleting existing table columns

Right now I'm doing a strpos search for ALTER, CREATE and DROP which should work.

Are there any other commands that do the things above and that I should include in my search?

Note that I don't need this for security reasons. I just need to know if the table schema changed, so I can update my local cache of the schema info...

Solution

  • One false positive could occur if ALTER, CREATE, or DROP occur within a string constant.

    INSERT INTO News (headline) VALUES ('Stocks DROP for no reason!');

    Also strpos() only looks for literal substrings, it has no idea if the substring is part of a longer word.

    SELECT * FROM CLOTHING_ALTERATIONS

    So you might want to use a regular expression and make sure the word is at the beginning of the statement, and is a whole word.

    $num_matches = preg_match("/^\s*(ALTER|CREATE|DROP|RENAME)\b/m", $sql);

    Using multi-line regexp matching is important if the string contains an SQL line comment.

    -- the following statement is run in my add_column() function
ALTER TABLE mytable ADD COLUMN ...

    It could be even more complex, because many implementations of SQL allow /* */ as delimiters for inline comments.

    /* added 12/7/2011 */ CREATE /* TEMPORARY */ TABLE mytable ...