What is the proper syntax for an UPDATE query in Postgres?

Question

I have an sample query of what i want to do but the syntax is in MySQL.

 $sql = "UPDATE tbl_sample SET ".$column_name."='".$text."' WHERE id='".$id."'";  

But it has to be done using Postgres syntax. And sorry, I'm just new at back-end programming.

here's the other src for some pages main.php

$(document).ready(function(){  
  function fetch_data()  
  {  
       $.ajax({  
            url:"dbselect.php",  
            method:"POST",  
            success:function(data){  
                 $('#live_data').html(data);  
            }  
       });  
  }  
  fetch_data();  
  $(document).on('click', '#btn_add', function(){  
       var desc = $('#desc').text();  
       var ipadd = $('#ipadd').text();
       var port = $('#port').text();
       var platform = $('#platform').text();

       if(desc == '')  
       {  
            alert("Enter Description");  
            return false;  
       }  
       if(ipadd == '')  
       {  
            alert("Enter IP Address");  
            return false;  
       }

       if(port == '')  
       {  
            alert("Enter Port Number");  
            return false;  
       }

       if(platform == '')  
       {  
            alert("Enter DB Platform");  
            return false;  
       } 
       $.ajax({  
            url:"dbinsert.php",  
            method:"POST",  
            data:{desc:desc, ipadd:ipadd, port:port, platform:platform},  
            dataType:"text",  
            success:function(data)  
            {  
                 alert(data);  
                 fetch_data();  
            }  
       }) 
  });  
  function edit_data(id, text, column_name)  
  {  
       $.ajax({  
            url:"dbedit.php",  
            method:"POST",  
            data:{id:id, text:text, column_name:column_name},  
            dataType:"text",  
            success:function(data){  
                 alert(data);  
            }  
       });  
  }   $(document).on('blur', '.desc', function(){  
       var id = $(this).data("id1");  
       var desc = $(this).text();  
       edit_data(id, desc, "desc");  
  });  
  $(document).on('blur', '.ipadd', function(){  
       var id = $(this).data("id2");  
       var ipadd = $(this).text();  
       edit_data(id,ipadd, "ipadd");  
  });

  $(document).on('blur', '.port', function(){  
       var id = $(this).data("id3");  
       var port = $(this).text();  
       edit_data(id,port, "port");  
  });

  $(document).on('blur', '.platform', function(){  
       var id = $(this).data("id4");  
       var platform = $(this).text();  
       edit_data(id,platform, "platform");  
  });  

  $(document).on('click', '.btn_delete', function(){  
       var id=$(this).data("id5");  
       if(confirm("Are you sure you want to delete this?"))  
       {  
            $.ajax({  
                 url:"dbdelete.php",  
                 method:"POST",  
                 data:{id:id},  
                 dataType:"text",  
                 success:function(data){  
                      alert(data);  
                      fetch_data();  
                 }  
            });  
       }  
    });  
 });

here's the insert query:

<?php  
   $connect = pg_connect("host=localhost dbname=dbinv user=postgres          password=moira port=5432");  
   $sql = "INSERT INTO \"kbc\".dbinventory(desc,ipadd,port,platform)
   values ('".$desc."','".$ipadd."','".$port."','".$platform."')"; 
   if(pg_query($connect, $sql))  
   {  
  echo 'Data Inserted';  
   }  
  ?> 

and the here's the edit query that I'm concerned about:

<?php  
$connect = mysqli_connect("localhost", "root", "", "test_db");  
$id = $_POST["id"];  
$text = $_POST["text"];  
$column_name = $_POST["column_name"];  
$sql = "UPDATE tbl_sample SET ".$column_name."='".$text."' WHERE   id='".$id."'";  
if(pg_query($connect, $sql))  
{  
  echo 'Data Updated';  
}  
?>

The database is done but still doesnt work. Am i overlooking or did i miss something or is it all wrong?? Please kindly look at these src and tell me which should i reinforce/change.

Answer 1

The actual problem is that you're constructing your query dangerously wrong. Most likely $text or $id has a quote in it, or similar, so the statement lands up being invalid syntax.

e.g. if $text is O'Reilly you'll get:

ERROR: ERROR:  syntax error at or near "Reilly"

Please read https://en.wikipedia.org/wiki/SQL_injection

You haven't given any details about the tools/language/etc you're using, but you should use the query parameter binding ("prepared statement") support for your language and database driver.

Assuming you're using PHP, please read:

• http://php.net/manual/en/security.database.sql-injection.php
• http://php.net/manual/en/function.pg-query-params.php
• http://php.net/manual/en/ref.pdo-pgsql.php

Do not "fix" this with use of regular expressions for homebrew quoting, or with unsafe functions like PHP's addslashes.

If it worked in MySQL, I assume you were using it without the ANSI QUOTES option enabled, and "double quoted" identifiers. Or you're trying to escape with backslashes, like 'O\'Reilly', which will not work in an ANSI SQL compliant database. You must double the quotes, like 'O''Reilly', instead. But see above; don't do that, use bind parameters in prepared statements instead.