Overwritte data in postgresql table php

Question

The data present in the postgresql table isn't update any more. I want to overwrite the data. When I just use insert into, new data is added but the old data remains. I tried to use update but then I get errors. I would like to update all records. I think it's probably something with the syntax. But I can't find the problem.

Code

$dbname = "dbtest";
$host = "localhost";
$username = "postgres";
$password = "pasword";

$dbh = new PDO("pgsql:dbname=$dbname; host=$host", $username, $password);

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$c = array("Human","Mouse","Rat","Hamster","SV40");       
$b = array("Human HBO gene", "Mouse BB gene", "Human CCB gene", "SV40 TP gene", "Hamster TP53 gene");
$count=0;
foreach($c as $key => $d){
    $e =$b[$key];
    $name = $count++;
    if (strpos($e, $d) !== FALSE) {
        $match = $d;
        $specie = $d;
        $specie = str_replace("Human","Homo Sapiens",$specie);
        $specie = str_replace("Mouse","Mus Musculus",$specie);
        $specie = str_replace("Rat","Rattus norvegicus",$specie);
        $Specie = str_replace("Hamster", "Mesocricetus Auratus",$specie);
        $specie = str_replace("SV40","Simian virus 40",$specie);
    }else{
        $match = "0";
        $specie = "0";
    }

echo $match. " ". $specie. " ";

$var_id = $name;
$var_match = $match;
$var_full_name = $specie;


    #$sql = "INSERT INTO species (id,match,full_name) VALUES ('".$var_id."','".$var_match ."','".$var_full_name."')";
    $sql = "UPDATE species SET id = '".$var_id."', match = '".$var_match ."', full_name='".$var_full_name."'";
    if ($dbh->query($sql)) {
        echo "New Record Inserted Successfully!<br \>\n";
    }else{
        echo "Data not successfully Inserted.<br \>\n";
    } 
}

The error I get:

Fatal error: Uncaught PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: > syntax error at or near "Sapiens" LINE 1: ...species SET id = '0', match = Human, full_name=Homo Sapiens' ^ in /var/www/html/test/Insert.php:59 Stack trace: #0 /var/www/html/test/Insert.php(59): PDO->query('UPDATE species ...') #1 {main} thrown in /var/www/html/test/Insert.php on line 59

Watch your cases: `$specie` and `$Specie`. In your query you probably need to add a `where id=$id` unless you want to update ALL records. If updating you also don't want to update the `id` of that specific record — brombeer, May 18 '18 at 08:22
hmm query you build is UPDATE species SET id = '0', match = 'Human', full_name='Homo Sapiens' , that doesnt fit to the error, cause of the quotes — FatFreddy, May 18 '18 at 08:56
I know that this specific example isn't showing direct evidence of using user input directly, but even so you REALLY shouldn't build SQL queries by appending strings. It's asking for trouble for both malicious reasons (http://bobby-tables.com/) and accidental issues (I've seen more than enough DB applications fall over when somebody entered their last name as O'Leary). PDO provides prepared statements, I strongly advise you use them. — GordonM, Jun 08 '18 at 12:00
Possible duplicate: https://stackoverflow.com/questions/7600661/mysql-error-when-inserting-data-containing-apostrophes-single-quotes — GordonM, Jun 08 '18 at 12:02

score 1 · Answer 1 · answered Jun 08 '18 at 11:54

1

You should use a select query to determine if the value is new or old. If old update data, else insert data.

FUNCTION

function execute_query($query,$dbh){
    if ($dbh->query($query)) {
        return "New Record Inserted Successfully!<br \>\n";
    }else{
        return "Data not successfully Inserted.<br \>\n";
    } 
}

SELECT

$query = 'SELECT * FROM tbl '. 'WHERE "test1" = '. 
        "'".$var_test."'" . 'AND "test2" = '. 
        "'".$var_test2."'";
        $stmt = $dbh->prepare($query);
        $stmt->execute();
        $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);

        $outcome = $stmt->fetch();

UPDATE

        if ($outcome !== false){
            $sql = "UPDATE tbl SET test1 = '".$var_test1."', test2 = '".$var_test2."', 
            test3 = '".$var_test3."'
            "WHERE id = '".$var_id."' ";
            execute_query($sql,$dbh);

INSERT

    }else{    
        $sql = "INSERT INTO genes 
        (id,test1,test2,test3) 
        VALUES ('".$var_id."','".$var_test1 ."','".$var_test2."','".$var_test3."')";
        execute_query($sql,$dbh);
    }

answered Jun 08 '18 at 11:54

ageans

549
5
20

These will all fail if any of the input ever includes an apostrophe (```'```) and all can be abused with SQL injection attacks. – GordonM Jun 08 '18 at 12:07
if a variable contain a (') you could set all variable to string before inserting using settype. settype($var_test1, "string"). The SQL injection attack would it be a problem if you use the code for your self to retrieve data. Do you have to adapt you code to prevent this attack then? – ageans Jun 08 '18 at 12:17
How is settype supposed to help? If the input starts off as a string with an apostrophe in it, it will still be a string with an apostrophe in it afterwards. Any variable you want to insert into any SQL query should be inserted via prepared statements. – GordonM Jun 08 '18 at 14:30
I don't know why it works, but it works in my case when a ' symbol is present in one of my inserting data. – ageans Jun 11 '18 at 09:46

score 0 · Accepted Answer · answered May 24 '18 at 08:32

0

When I use the following syntax, the error is gone. This query needs to be used to update.

$sql = "UPDATE species SET match ='".$var_match ."', full_name='".$var_full_name."' WHERE id = '".$var_id."' ";

answered May 24 '18 at 08:32

Nemo

503
6
23

It will work until any of your substituted variables contains a ```'``` character at which point it will die. And Goodness help you if anybody enters ```Robert'); DROP TABLE some_table_full_of_vital_data;--``` into your application! http://bobby-tables.com/ Please learn to use PDO's prepared statement feature. – GordonM Jun 08 '18 at 12:04