Determine if a SQL query alters the database structure

Question

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...

A better appraoch is to not allow persmiisions for users to do such things, only an admin should ever be creating or altering or delting tables and no user shoudl have the rights. — HLGEM, Dec 07 '11 at 20:06
srsly why r u +1'ing the comment above? I just wrote that this is not for security reasons :) — Alex, Dec 07 '11 at 20:07
And how do we know that this isn't an administrative interface? Plenty of software has an administrative interface that allows people to change tables. Usually these are user-defined tables, and the core app tables are not allowed, but this is a legitinmate design. — David, Dec 07 '11 at 20:09
Is your app itself intended to allow users to modify the schema? Do they do this through some UI, or can users actually type in SQL they wish to run? — Mike Christensen, Dec 07 '11 at 20:09
@david: you're right:( I guess I'll expire the cache once a day or something... — Alex, Dec 07 '11 at 20:10
I was gonna suggest triggers on the sys tables, but looks like someone beat me to it.. That's a great route! — Mike Christensen, Dec 07 '11 at 20:12
@MikeChristensen - DDL triggers are not "triggers on sys.tables", firstly that is a view and secondly you would not be allowed to create triggers on system base tables. — Martin Smith, Dec 07 '11 at 20:13
Ah, that's a good idea too then provided the database in question supports it. — Mike Christensen, Dec 07 '11 at 20:18

score 4 · Answer 1 · answered Dec 07 '11 at 20:10

4

For some DBMS (PostgreSQL, MS SQL Server, Informix) SELECT ... INTO ... can create a new table.

answered Dec 07 '11 at 20:10

In MYSQL, there is also `RENAME` (database or table). – ypercubeᵀᴹ Dec 07 '11 at 20:11

score 4 · Answer 2 · answered Dec 07 '11 at 20:11

4

This will be pretty difficult to do through string checks.

There are other ways to make changes like:

SELECT *
INTO SomeTable
FROM SomeOtherTable

sp_rename 'OldTable' 'NewTable'

Security or DDL triggers probably are a better choice.

answered Dec 07 '11 at 20:11

JNK

63,321
15
122
138

Bill Karwin · Accepted Answer · 2011-12-07T20:25:25.177

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 ...

Determine if a SQL query alters the database structure

3 Answers3