我试图实施UPSERT的问题 [英] Problems with my attempt to implement an UPSERT

问题描述

我在检查条件以更新PostgreSQL中的表时遇到此问题。它必须检查用户是否下载了一次，如果是，则在 acessos 中添加+1。

I'm having this problem when checking a condition to update a table in PostgreSQL. It has to check if the user download this once and if yes, add +1 in acessos.

<?php
$result2 = pg_query("SELECT * from downloads WHERE (nome = $_POST[nome_download] AND email = $_POST[email_download])");
if (pg_num_rows($result2) == 0){
$result = pg_query("INSERT INTO downloads (nome, email, estado, arquivo, acessos) VALUES ('$_POST[nome_download]','$_POST[email_download]','$_POST[estado_download]','$_SESSION[nome_arquivo_download]','1')");
}else{
$arr[acessos] = $arr[acessos] + 1;
$result = pg_query("UPDATE downloads SET acessos = $arr[acessos] WHERE (nome = $_POST[nome_download] AND email = $_POST[email_download])");
}


if (!$result){
echo "Não foi possível realizar o cadastro. Tente fazer o download mais tarde.";
}
else
{
echo "soft_bd";
pg_close();
}
?>

推荐答案

你指的是 $ arr 但是从您发布的代码中分配的内容并不明显。无论哪种方式，如果你想将 acessos 的当前值增加1，这种方法在多用户环境中完全不安全。

You refer to $arr but it's not evident from your posted code where that is assigned. Either way, if you want to increase the current value of acessos by 1, this approach is completely unsafe in a multi-user environment.

您也完全接受 SQL注入。请改用预备语句。

You are also completely open to SQL injection. Use prepared statements instead.

在 Postgres 9.5 中，您甚至可以在 单一语句中执行此操作/ strong>使用新的 UPSERT实施 INSERT ... ON CONFLICT ON ... DO UPDATE - 假设有 UNIQUE 或 PRIMARY KEY 约束（nome，email）：

In Postgres 9.5 you can even do this in a single statement with the new UPSERT implementation INSERT ... ON CONFLICT ON ... DO UPDATE - assuming there is a UNIQUE or PRIMARY KEY constraint on (nome, email):

$sql = 'INSERT INTO downloads AS d (nome, email, estado, arquivo, acessos)
        VALUES ($1, $2, $3, $4, 1)
        ON CONFLICT ON (nome, email) DO UPDATE 
        SET    acessos = EXCLUDED.acessos + 1';

对于重复通话，您可以使用 pg_prepare 和 pg_execute 。对于单个呼叫，请使用 pg_query_params ：

For repeated calls, you might use pg_prepare and pg_execute. For a single call use pg_query_params:

pg_query_params($sql, array($_POST[nome_download]
                          , $_POST[email_download]
                          , $_POST[estado_download]
                          , $_SESSION[nome_arquivo_download]));

这篇关于我试图实施UPSERT的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！