Wrapping words in a SQL query string with regex

Question

I have a database class that is written in PHP and it should take care of some things I don't want to care about. One of these features is handling the decryption of columns that are encoded with the AES function of MySQL.
This works perfect in normal cases (which in my opinion means that there is no alias in the query string "AS bla_bla"). Lets say that someone writes a query string that contains an alias, which contains the name of a column the script should decrypt, the query dies, because my regex wraps not only the column, but the alias as well. That is not how its supposed to be.

This is the regex I've written:

preg_replace("/(((\`|)\w+(\`|)\.|)[encrypted|column|list])/i", "AES_DECRYPT(${0},'the hash')"

The part with the grave accents is there because sometimes the query does contain the table name which is either inside of grave accents or not.

An example input:

SELECT encrypted, something AS 'a_column' FROM a_table;

An example output:

SELECT AES_DECRYPT(encrypted, 'the hash'), something AS 'a_AES_DECRYPT(column, 'the hash')' FROM a_table;

As you can see, this is not going to work, so my idea was to search only for words, that are not right after the word 'as' until a special character or a white space appears. Of course i tried it hours to work, but I don't get the correct syntax.

Is it possible to solve this with pure regex and if yes how would it look like?

Thanks for mentioning it, i added it to the question

therufa
– therufa

2012-05-16 17:28:18 +00:00
Commented May 16, 2012 at 17:28 — therufa
– therufa, Commented May 16, 2012 at 17:28

Mihai Stancu · Accepted Answer · 2012-05-17 20:15:13Z

1

This should get you started:

$quoted_name = '(\w+|`\w+`|"\w+"|\'\w+\')';
preg_match("/^SELECT ((, )?$quoted_name( AS $quoted_name)?)* FROM $quoted_name;$/", "SELECT encrypted, something AS 'a_column' FROM a_table;", $m);
var_dump($m);

The replacement parts should be easy to spot an write after you study the var_dump.

answered May 17, 2012 at 20:15

Mihai Stancu

16.2k2 gold badges35 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Wrapping words in a SQL query string with regex

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related