将数组中的$ _POST数据直接插入MySQL语句有多安全？

Question

I have created a simple function to post data into a mysql table using a function and an array. The array is built from $_POST items on a form. What I would like to know is: Are there potential security holes that I am not seeing?

Here is the function:

            public function add_sql_data($table,$array){
            $tot = count($array);
            $c=0;
            foreach($array as $k => $v){
                $fields = $fields.$k;
                $values = $values."'".$v."'";
                $c++;
                if($c < $tot){
                    $fields = $fields.","; 
                    $values = $values.","; 
                }
            }
            $sql = "INSERT INTO ".$table."(".$fields.") values(".$values.")";
            if (mysql_query($sql)){
                return "succesfull";
            }else{
                return "error";
            }
        }

I have tried to use the small amount of PHP I know to create a SQL injection, but it seams to me that the array is actually stopping any harmful syntax to run. Thanks!!

Answer 1

Not safe at all

In the sql in the question - there is no sanitization or escaping. Consider what will happen with individual form inputs like this:

• 'and he said "no way jose"'
• 'blah ")'
• '"), (select field from table limit 1))--'

In the first, there's going to a syntax error of some sort, and also in the second.

The 3rd is just a hint at the tip of an iceberg of the sort of injection you are making possible. Here, we're injecting the value of another table into this insert statement, if that field is visible to the end user they can do things like this to explore your database and gain access to information they shouldn't be able to see. Just google sql injection and you'll find an endless list of examples.

The right way

You really, really should be using PDO and preferably prepared statements instead of the older mysql_* functions.

An example of something similar to what you're doing that is safe from injection is:

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$stmt = $dbh->prepare("INSERT INTO table (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

In this way, it doesn't matter what $name or $value are - they will be escaped appropriately.