2

In my insert method of my database class I pass the parameters as an array:

$sql='INSERT INTO `'.$table.'` (`'.implode('`, `',array_keys($params)).'`) VALUES ("' . implode('", "', $params) . '")';

As you can see, the array gets imploded.

My $params for example looks like this:

$params = [
  'field1' => 'sometext1',
  'field2' => 'sometext2',
  'stamp_created' => NULL
]

My field 'stamp_created' must be set NULL, so that the date of creation gets displayed in the data row in the database. However, imploding the array turns NULL to an empty string "":

VALUES ('sometext1', 'sometext2', '')

How do I achieve that NULL does not get ignored in my implode operation? My desired result is:

VALUES ('sometext1', 'sometext2', NULL)
Improve this question
2
  • 1
    Sorry I downvoted your question but your approach is terribly insecure and allow SQL injections. You shouldn't built your query like that. You should use prepared statements, it's lot safer Commented Mar 25, 2016 at 9:50
  • 1
    @Tom, you may not be working directly with the databases. When it is necessary to convert information from one database to another, the conditions for SQL injection did not exist in general. Commented Oct 9, 2024 at 5:44

2 Answers 2

Reset to default
3

A literal answer to this question obviously doesn't exist. It's impossible to keep PHP NULL when using implode(), because implode() will cast all array values to string and PHP's null, when cast to a string, results in an empty string.

While if you need to send a PHP null to database, then you must use prepared statements instead of implode(), which is the only proper way for database interaction (and will solve this null problem just as a side effect).

Therefore your code should be like

$params = ['sometext1','sometext2', NULL];
$sql = "INSERT INTO `table` (field1, field2, stamp_created) VALUES (?,?,?)";
$db->prepare($sql)->execute($params);
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

What they REALLY want is prepared statement
-3

I am working directly with the databases. When it is necessary to convert information from one database to another, the conditions for SQL injection did not exist in general. I am using this solution to prepare an INSERT SQL query for 10000-50000 rows at a time.

In my opinion, it is better to immediately convert the array into the necessary SQL expression.

function implode_flds(array $row){
    $str = '';
    $del = '';
    
    foreach ($row as $f) {
        if (is_null($f) or strtoupper($f) == 'NULL'){
            $f = 'NULL';
        } elseif (is_string($f)){
            $f = '\'' . $f . '\'';
        }
        $str .= $del.$f;
        $del = ',';
    }
    
    return $str;
}
Improve this answer

15 Comments

In my opinion, this code snippet is outdated and is prone to SQL injections
@DarkBee, you may not be working directly with the databases. When it is necessary to convert information from one database to another, the conditions for SQL injection did not exist in general. I am using this solution to prepare an INSERT SQL query for 10000 rows at a time.
No, you should use prepared statements and foresee placeholders rather than the actual value
phpize.online/sql/mysql80/a239bfe685b771302e0cc07bae06d8c9/php/…
It has been posted hundreds times already
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.