I have DAL class and I have done Query Parametrization to avoid SQL injection. As you can see the weakpoing for Dynamic Query is table_name and column name. I have made small function to (hopefully) validate table variable before I can add to SQL command string. Now I was struggling if I should do the same for columns. Someone here have proposed that I make a whitelist. Since it is not guaranteed that I alone will use the DA: then it is a challenge on how to make that white list. I have tried to think of post install script that *MUST* be called fter installation which colllects whitelits for tables and columns. That will make it easy to validate tables and columns but then, I will have added overhead and restrictions to users. So my questions are:
1. Is Regex function like this enough to validate tables name?
the function in question:

private function validate_table($table) {
        //validates that the table contains no character than A-Z 0-9  _- 
        $regex = '/^[a-zA-Z0-9_$]+$/'; //[0-9,a-z,A-Z$_]
        if (preg_match($regex, $table)) {
            return true;
        } else {
            return false;

2. Is Regex approach like used in 1 suitable for columns too?
3. Is post-install script approach worth of adding trouble?
4. How do you do to protect against SQL injection in your dynamic query if you have any?

Recommended Answers

All 2 Replies

Why not get a list of table and columns from mysql, and validate against those (SHOW TABLES and SHOW COLUMNS).

I just did that and forgot to close thread.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.