notice that we're also using MySQL functions within values
$db->insert(
'table',
array(
'column1' => 'value1',
'column2' => 'TRIM(UCASE("value2"))',
'date_updated' => 'NOW()',
));
when using MySQL functions, the value will be used as it is without being escaped!
while this is ok when using a function without any arguments like NOW(), this may
pose a security concern if the argument(s) come from user input.
in this case we have to escape the value ourselves
$db->insert(
'table',
array(
'column1' => 'value1',
'column2' => 'TRIM(UCASE("' . $db->escape($value) . '"))',
'date_updated' => 'NOW()',
));
| public insert ( string $table, array $columns, boolean $ignore = false, boolean $highlight = false ) : boolean | ||
| $table | string | Table in which to insert. @param array $columns An associative array where the array's keys represent the columns names and the array's values represent the values to be inserted in each respective column. Column names will be enclosed in grave accents " ` " (thus, allowing seamless usage of reserved words as column names) and values will be automatically {@link escape()}d in order to prevent SQL injections. You may also use any of {@link http://www.techonthenet.com/mysql/functions/ MySQL's functions} as values. Be aware that when using MySQL functions, the value will be used as it is without being escaped! While this is ok when using a function without any arguments like NOW(), this may pose a security concern if the argument(s) come from user input. In this case make sure you {@link escape} the values yourself! @param boolean $ignore (Optional) By default trying to insert a record that would cause a duplicate entry for a primary key would result in an error. If you want these errors to be skipped set this argument to TRUE. For more information see {@link http://dev.mysql.com/doc/refman/5.5/en/insert.html MySQL's INSERT IGNORE syntax}. Default is FALSE. @param boolean $highlight (Optional) If set to TRUE the debugging console will be opened automatically and the query will be shown - really useful for quick and easy debugging. Default is FALSE. @since 1.0.9 @return boolean Returns TRUE on success of FALSE on error. |
| $columns | array | |
| $ignore | boolean | |
| $highlight | boolean | |
| return | boolean | |
function insert($table, $columns, $ignore = false, $highlight = false)
{
// enclose the column names in grave accents
$cols = '`' . implode('`,`', array_keys($columns)) . '`';
$values = '';
// iterate through the given columns
foreach ($columns as $column_name => $value) {
// separate values by comma
$values .= $values != '' ? ', ' : '';
// if value is a MySQL function
if ($this->_is_mysql_function($value)) {
// use it as it is
$values .= $value;
// we don't need this value in the replacements array
unset($columns[$column_name]);
// if not a MySQL function, use a marker
// that we'll replace with the value from the replacements array
} else {
$values .= '?';
}
}
// run the query
$this->query('
INSERT' . ($ignore ? ' IGNORE' : '') . ' INTO
`' . $table . '`
(' . $cols . ')
VALUES
(' . $values . ')', array_values($columns), false, false, $highlight);
// return true if query was executed successfully
if ($this->last_result) {
return true;
}
return false;
}