Table expression and columns are not escaped and are not safe for user-input.
| public insert ( string $tableExpression, array $data, array $types = [] ) : integer | ||
| $tableExpression | string | The expression of the table to insert data into, quoted or unquoted. |
| $data | array | An associative array containing column-value pairs. |
| $types | array | Types of the inserted data. |
| return | integer | The number of affected rows. |
/**
* @param string $tableName
* @param array $rows
*/
protected function insertTableRows($tableName, array $rows)
{
foreach ($rows as $rowKey => $values) {
$this->connection->insert($tableName, $this->parser->parse($values));
$this->parser->addReference($rowKey, $this->connection->lastInsertId());
}
}
public function save(Order $order)
{
$data = $order->jsonSerialize();
unset($data['id']);
$this->connection->insert($this->getTableName(), $data);
$order->setId($this->connection->lastInsertId());
}
/**
* Saves the pool to the database.
*
* @param \MusicBox\Entity\Like $pool
*/
public function save($pool)
{
$poolData = array('address_id' => $pool->getAddress()->getId(), 'access_info' => $pool->getAccessInfo());
if ($pool->getId()) {
$this->db->update('pools', $poolData, array('pool_id' => $pool->getId()));
$newFile = $this->handleFileUpload($item);
if ($newFile) {
$poolData['image'] = $pool->getImage();
}
} else {
// The pool is new, note the creation timestamp.
$poolData['created_at'] = time();
$this->db->insert('pools', $poolData);
// Get the id of the newly created pool and set it on the entity.
$id = $this->db->lastInsertId();
$pool->setId($id);
// If a new image was uploaded, update the pool with the new
// filename.
$newFile = $this->handleFileUpload($pool);
if ($newFile) {
$newData = array('image' => $pool->getImage());
$this->db->update('pools', $newData, array('pool_id' => $id));
}
}
}
/**
* Saves the artist to the database.
*
* @param \MusicBox\Entity\Artist $artist
*/
public function save($artist)
{
$artistData = array('name' => $artist->getName(), 'short_biography' => $artist->getShortBiography(), 'biography' => $artist->getBiography(), 'soundcloud_url' => $artist->getSoundCloudUrl(), 'image' => $artist->getImage());
if ($artist->getId()) {
// If a new image was uploaded, make sure the filename gets set.
$newFile = $this->handleFileUpload($artist);
if ($newFile) {
$artistData['image'] = $artist->getImage();
}
$this->db->update('artists', $artistData, array('artist_id' => $artist->getId()));
} else {
// The artist is new, note the creation timestamp.
$artistData['created_at'] = time();
$this->db->insert('artists', $artistData);
// Get the id of the newly created artist and set it on the entity.
$id = $this->db->lastInsertId();
$artist->setId($id);
// If a new image was uploaded, update the artist with the new
// filename.
$newFile = $this->handleFileUpload($artist);
if ($newFile) {
$newData = array('image' => $artist->getImage());
$this->db->update('artists', $newData, array('artist_id' => $id));
}
}
}
/**
* Saves the user to the database.
*
* @param \MusicBox\Entity\User $user
*/
public function save($user)
{
$userData = array('username' => $user->getUsername(), 'mail' => $user->getMail(), 'role' => $user->getRole());
// If the password was changed, re-encrypt it.
if (strlen($user->getPassword()) != 88) {
$userData['salt'] = uniqid(mt_rand());
$userData['password'] = $this->encoder->encodePassword($user->getPassword(), $userData['salt']);
}
if ($user->getId()) {
// If a new image was uploaded, make sure the filename gets set.
$newFile = $this->handleFileUpload($user);
if ($newFile) {
$userData['image'] = $user->getImage();
}
$this->db->update('users', $userData, array('user_id' => $user->getId()));
} else {
// The user is new, note the creation timestamp.
$userData['created_at'] = time();
$this->db->insert('users', $userData);
// Get the id of the newly created user and set it on the entity.
$id = $this->db->lastInsertId();
$user->setId($id);
// If a new image was uploaded, update the user with the new
// filename.
$newFile = $this->handleFileUpload($user);
if ($newFile) {
$newData = array('image' => $user->getImage());
$this->db->update('users', $newData, array('user_id' => $id));
}
}
}
/**
* @param ReminderWasAddedToTodo $event
* @return void
*/
public function onReminderWasAddedToTodo(ReminderWasAddedToTodo $event)
{
// remove other reminder for todo first
$this->connection->delete(Table::TODO_REMINDER, ['todo_id' => $event->todoId()->toString()]);
$reminder = $event->reminder();
$this->connection->insert(Table::TODO_REMINDER, ['todo_id' => $event->todoId()->toString(), 'reminder' => $reminder->toString(), 'status' => $reminder->status()->toString()]);
}
public function executeUpgrade(Connection $connection)
{
// update action class names
$actions = ['Fusio\\Action\\BeanstalkPush' => 'Fusio\\Impl\\Action\\MqBeanstalk', 'Fusio\\Action\\CacheResponse' => 'Fusio\\Impl\\Action\\CacheResponse', 'Fusio\\Action\\Composite' => 'Fusio\\Impl\\Action\\Composite', 'Fusio\\Action\\Condition' => 'Fusio\\Impl\\Action\\Condition', 'Fusio\\Action\\HttpRequest' => 'Fusio\\Impl\\Action\\HttpRequest', 'Fusio\\Action\\Pipe' => 'Fusio\\Action\\Pipe', 'Fusio\\Action\\RabbitMqPush' => 'Fusio\\Impl\\Action\\MqAmqp', 'Fusio\\Action\\SqlExecute' => 'Fusio\\Impl\\Action\\SqlExecute', 'Fusio\\Action\\SqlFetchAll' => 'Fusio\\Impl\\Action\\SqlFetchAll', 'Fusio\\Action\\SqlFetchRow' => 'Fusio\\Impl\\Action\\SqlFetchRow', 'Fusio\\Action\\StaticResponse' => 'Fusio\\Impl\\Action\\StaticResponse'];
foreach ($actions as $oldClass => $newClass) {
$connection->executeUpdate('UPDATE fusio_action SET class = :new_class WHERE class = :old_class', ['new_class' => $newClass, 'old_class' => $oldClass]);
}
// update connection class names
$actions = ['Fusio\\Connection\\Beanstalk' => 'Fusio\\Impl\\Connection\\Beanstalk', 'Fusio\\Connection\\DBAL' => 'Fusio\\Impl\\Connection\\DBAL', 'Fusio\\Connection\\DBALAdvanced' => 'Fusio\\Impl\\Connection\\DBALAdvanced', 'Fusio\\Connection\\MongoDB' => 'Fusio\\Impl\\Connection\\MongoDB', 'Fusio\\Connection\\Native' => 'Fusio\\Impl\\Connection\\Native', 'Fusio\\Connection\\RabbitMQ' => 'Fusio\\Impl\\Connection\\RabbitMQ'];
foreach ($actions as $oldClass => $newClass) {
$connection->executeUpdate('UPDATE fusio_connection SET class = :new_class WHERE class = :old_class', ['new_class' => $newClass, 'old_class' => $oldClass]);
}
// update routes class names
$routes = $connection->fetchAll('SELECT id, controller FROM fusio_routes');
foreach ($routes as $route) {
if (substr($route['controller'], 0, 6) == 'Fusio\\' && substr($route['controller'], 0, 11) != 'Fusio\\Impl\\') {
$newController = 'Fusio\\Impl\\' . substr($route['controller'], 6);
$connection->executeUpdate('UPDATE fusio_routes SET controller = :controller WHERE id = :id', ['controller' => $newController, 'id' => $route['id']]);
}
}
// insert new classes table
$data = $this->getInstallInserts();
if (isset($data['fusio_connection_class'])) {
foreach ($data['fusio_connection_class'] as $row) {
$connection->insert('fusio_connection_class', $row);
}
}
if (isset($data['fusio_action_class'])) {
foreach ($data['fusio_action_class'] as $row) {
$connection->insert('fusio_action_class', $row);
}
}
}
/**
* @return JsonResponse
*/
public function collect()
{
$toSave = array();
$count = 0;
$currentPageIndex = 0;
while ($count < self::NB_POSTS) {
$url = self::getUrl($currentPageIndex++);
try {
$html = $this->curlUtils->getResource($url);
$posts = $this->postParserUtils->getPosts($html);
foreach ($posts as $post) {
if ($count === self::NB_POSTS) {
break;
}
$postContent = $this->postParserUtils->parse($post);
$toSave[$count++] = $postContent;
}
} catch (\Exception $exception) {
return new JsonResponse($exception);
}
}
for ($i = 0; $i < count($toSave); $i++) {
$this->dbal->insert(self::MODEL, $toSave[$i]);
}
$toReturn = count($toSave) . " posts have been added";
return new JsonResponse($toReturn);
}
public function save($user)
{
$userData = array('name' => $user->getName(), 'email' => $user->getEmail(), 'password' => $user->getPassword(), 'joinTime' => $user->getJoinTime(), 'registerIp' => $user->getRegisterIp(), 'sharedKey' => $user->getSharedKey(), 'integration' => $user->getIntegration(), 'shareKey' => $user->getShareKey());
$this->db->insert('user', $userData);
$id = $this->db->lastInsertId();
$user->setId($id);
}
public function create(CacheRecord $cacheRecord)
{
$this->db->insert($this->entityTable, $cacheRecord->toArray());
if ($this->db->lastInsertId() <= 0) {
throw new InvalidArgumentException("The insert failed.");
}
return $this->db->lastInsertId();
}
/**
* {@inheritdoc}
*/
protected function write(array $record)
{
$record = $record['formatted'];
try {
$this->connection->insert($this->tableName, $record);
} catch (\Exception $e) {
}
}
/**
* Sets an item in the store.
*
* @param string $key
* @param mixed $value
*
* @return void
*/
public function set($key, $value)
{
if ($this->exists($key)) {
$this->db->update('configurations', ['value' => $value], ['code' => $key]);
return;
}
// Insert it
$this->db->insert('configurations', ['code' => $key, 'value' => $value]);
}
/**
* @param Album $album
* @param int $id
* @return int The number of affected rows
*/
public function save(Album $album, $id = null)
{
$data = $album->getArrayCopy();
if (null === $id) {
return $this->db->insert('album', $data);
} else {
return $this->db->update('album', $data, ['id' => $id]);
}
}
/**
* @param PublishedPost $publishedPost
*
* @return void
*/
public function save(PublishedPost $publishedPost)
{
$data = ['title' => $publishedPost->title, 'content' => $publishedPost->content, 'category' => $publishedPost->category];
try {
$this->connection->insert('published_posts', array_merge($data, ['id' => $publishedPost->id]));
} catch (\Doctrine\DBAL\DBALException $e) {
$this->connection->update('published_posts', $data, ['id' => $publishedPost->id]);
}
}
private function insertRegisteredUser(UserRegistered $payload)
{
$data = [];
$data['userIdentifier'] = $payload->userIdentifier()->toString();
$data['username'] = $payload->username()->toString();
$data['hashedPassword'] = $payload->hashedPassword()->toString();
$data['at'] = $payload->at()->format('Y-m-d H:i:s');
$this->connection->insert($this->table, $data);
}
/**
* @param string $location
* @param array $data
*/
public function cacheSearchResult($location, $data)
{
$qb = $this->db->createQueryBuilder()->select('*')->from('search_cache', 's')->where('s.location = :location')->setParameter('location', strtolower($location))->execute();
if (count($qb->fetchAll()) === 0) {
$this->db->insert('search_cache', ['location' => strtolower($location), 'cache' => serialize($data), 'time' => date('Y-m-d H:i:s')]);
return;
}
$this->db->update('search_cache', ['cache' => serialize($data), 'time' => date('Y-m-d H:i:s')], ['location' => strtolower($location)]);
}
/**
* Save a snapshot
*
* @param Snapshot $snapshot
* @return void
*/
public function save(Snapshot $snapshot)
{
$table = $this->getTable($snapshot->aggregateType());
$this->connection->insert($table, ['aggregate_type' => $snapshot->aggregateType()->toString(), 'aggregate_id' => $snapshot->aggregateId(), 'last_version' => $snapshot->lastVersion(), 'created_at' => $snapshot->createdAt()->format('Y-m-d\\TH:i:s.u'), 'aggregate_root' => serialize($snapshot->aggregateRoot())], ['string', 'string', 'integer', 'string', 'blob']);
$queryBuilder = $this->connection->createQueryBuilder();
$table = $this->getTable($snapshot->aggregateType());
$queryBuilder->delete($table)->where('aggregate_type = :aggregate_type')->andWhere('aggregate_id = :aggregate_id')->andWhere('last_version < :last_version')->setParameter('aggregate_type', $snapshot->aggregateType()->toString())->setParameter('aggregate_id', $snapshot->aggregateId())->setParameter('last_version', $snapshot->lastVersion());
$queryBuilder->execute();
}
/**
* {@inheritdoc}
*/
public function save(Album $album)
{
$data = $album->getArrayCopy();
if (null === $album->getId()) {
return $this->db->insert('album', $data);
} else {
return $this->db->update('album', ['artist' => $data['artist'], 'title' => $data['title']], ['id' => $data['id']]);
}
}
/**
* @param string $tableName
* @param array $where
* @return int|false
*/
public function delete($tableName, array $where)
{
$data = $this->fetch($tableName, $where);
if (empty($data)) {
return false;
}
$data['deleted'] = 1;
return $this->connection->insert($tableName, $data);
}
public function persistObject($object)
{
$data = $this->preparePersistChangeSet($object);
$this->connection->insert($this->getTableName(), $data);
$class = $this->objectManager->getClassMetadata(get_class($object));
if (!isset($data[$class->identifier[0]])) {
$data[$class->identifier[0]] = $this->connection->lastInsertId();
}
return $data;
}
/**
* Traces the search result into the s_statistic_search
*
* @param Criteria $criteria
* @param ProductNumberSearchResult $result
* @param Shop $shop
*/
public function logResult(Criteria $criteria, ProductNumberSearchResult $result, Shop $shop)
{
if (!$criteria->hasCondition('search')) {
return;
}
/* @var $condition SearchTermCondition */
$condition = $criteria->getCondition('search');
$now = new \DateTime();
$this->connection->insert('s_statistics_search', ['datum' => $now->format('Y-m-d H:i:s'), 'searchterm' => $condition->getTerm(), 'results' => $result->getTotalCount(), 'shop_id' => $shop->getId()]);
}
/**
* Inserts a table row with specified data.
*
* @param array $data An associative array containing column-value pairs.
* @return integer The number of affected rows.
*/
public function insert(array $data)
{
if (!array_key_exists($this->createdAtRowName, $data)) {
$data[$this->createdAtRowName] = $this->now->format($this->dateFormat);
}
if (array_key_exists($this->updatedAtRowName, $data)) {
unset($data[$this->updatedAtRowName]);
}
return $this->conn->insert($this->getTableName(), $data);
}
public function save($cardKey)
{
$cardKeyId = $cardKey->getCardKey();
$cardKeyClassId = $cardKey->getCardKeyClassId();
$status = $cardKey->getStatus();
$createdTime = $cardKey->getCreatedTime();
$cardKeyData = array('cardKey' => $cardKeyId, 'cardKeyClassId' => $cardKeyClassId, 'status' => $status, 'createdTime' => $createdTime);
$result = $this->db->insert('cardKey', $cardKeyData);
return $result ? 1 : 0;
}
/**
* {@inheritdoc}
*/
public function add($data)
{
$data = $this->quoteColumns($data);
try {
$this->createMissingColumns($data);
$this->connection->insert($this->tableName, $data);
} catch (DBALException $e) {
throw DatabaseException::fromDBALException($e);
}
return $this->connection->lastInsertId();
}
/**
* Saves the produit to the database.
*
* @param Produit $produit
*
* @return Produit $produit
*/
public function save($produit)
{
$produitData = array('nom' => $produit->getNom(), 'prix' => $produit->getPrix(), 'type' => $produit->getType(), 'image' => $produit->getImage(), 'restaurant' => $produit->getRestaurant());
if ($produit->getId()) {
$this->db->update('produits', $produitData, array('id' => $produit->getId()));
} else {
$this->db->insert('produits', $produitData);
$last = $this->db->lastInsertId();
return $this->find($last);
}
}
/**
* @param integer $logId
* @param \Exception $exception
*/
public function appendError($logId, \Exception $exception)
{
if ($exception instanceof DisplayException) {
return;
}
$previousException = $exception->getPrevious();
if ($previousException instanceof \Exception) {
$this->appendError($logId, $previousException);
}
$this->connection->insert('fusio_log_error', array('logId' => $logId, 'message' => $exception->getMessage(), 'trace' => $exception->getTraceAsString(), 'file' => $exception->getFile(), 'line' => $exception->getLine()));
}
/**
* Saves the like to the database.
*
* @param Like $like
*
* @return Like $like
*/
public function save($like)
{
$likeData = array('restaurant_id' => $like->getRestaurant(), 'user_id' => $like->getUser());
if ($like->getId()) {
$this->db->update('likes', $likeData, array('id' => $like->getId()));
} else {
$this->db->insert('likes', $likeData);
$last = $this->db->lastInsertId();
return $this->find($last);
}
}
/**
* Saves the menu to the database.
*
* @param Menu $menu
*
* @return Menu $menu
*/
public function save($menu)
{
$menuData = array('nom' => $menu->getNom(), 'prix' => $menu->getPrix(), 'restaurant_id' => $menu->getRestaurant(), 'primary_id' => $menu->getProduit(), 'boisson' => $menu->getBoisson(), 'dessert' => $menu->getDessert());
if ($menu->getId()) {
$this->db->update('menus', $menuData, array('id' => $menu->getId()));
} else {
$this->db->insert('menus', $menuData);
$last = $this->db->lastInsertId();
return $this->find($last);
}
}
/**
* {@inheritDoc}
*/
public function set($key, $value)
{
if (!$this->tableExists()) {
$this->createTable();
}
if ($this->has($key)) {
$this->conn->update(self::TABLE, array($this->valueColumn => serialize($value)), array($this->keyColumn => $this->generateKey($key)));
return;
}
$this->conn->insert(self::TABLE, array($this->keyColumn => $this->generateKey($key), $this->valueColumn => serialize($value)));
}
public function save(Article $article)
{
$articleData = array('title' => $article->getTitle(), 'content' => $article->getContent());
if ($article->getId()) {
$this->db->update('article', $articleData, array('id' => $article->getId()));
} else {
$this->db->insert('article', $articleData);
$id = $this->getDb()->lastInsertId();
$article->setId($id);
}
}
