PHP Insert into PostgreSQL

Question

So it's probably a really stupid/basic question, but i have this simple PHP function (which works) and inserts data into a PostgreSQL DB.

My issue is when it encounters specific data;

function insertData($pg, $csvfile)
      {
        $x = 0;
        foreach ($csvfile as $data)
        {
          $email = $csvfile[$x]['email'];
          $name = $csvfile[$x]['name'];
          $surname = $csvfile[$x]['surname'];
          $query = "INSERT INTO users (email, name, surname) VALUES ('$email', '$name', '$surname')";
          $result = pg_query($pg, $query);
          $x++;
        }
      }

And while this works, it falls over with a surname such as:

O'hare

And obviously this occurs because then the PHP code comes out as:

...VALUES ('john@example.com', 'John', 'O'hare')";

but im not sure of how i should be structuring the PHP to allow for this.

Using PDO statement you resolve your problem's [http://php.net/manual/en/class.pdo.php](http://php.net/manual/en/class.pdo.php) — Sfili_81, Feb 12 '19 at 14:18
Possible duplicate of [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Karsten Koop, Feb 12 '19 at 14:30

score 4 · Accepted Answer · edited Sep 12 '20 at 13:15

4

Try this:

function insertData($pg, $csvfile) {
    $nbr = count(file($csvfile));   
    for($i=0; $i<$nbr; $i++) {
      $email = pg_escape_string( $csvfile[$i]['email'] );
      $name = pg_escape_string( $csvfile[$i]['name'] );
      $surname = pg_escape_string( $csvfile[$i]['surname'] );
      $query = "INSERT INTO users (email, name, surname) VALUES ('$email', '$name', '$surname')";
      $result = pg_query($pg, $query);
      if (!$result) {
        echo "Error while executing the query: " . $query;
        exit;
      }
    }
}

edited Sep 12 '20 at 13:15

Irshad Khan

5,670
2
44
39

answered Feb 12 '19 at 14:25

toh19

1,083
10
21

1

I would pg_escape_string the email too, just by principle. – Adder Feb 12 '19 at 14:28

Pascal Tovohery · Answer 2 · 2019-02-12T14:55:08.757

Solution using prepared query

function insertData($dbname, $tbname, $csvfile)
{
  $result = [];
  // Connect to a database named "mary"
  $dbconn = pg_connect("dbname=$dbname");

  // Prepare a query for execution
  $result = pg_prepare($dbconn, "my_query", 'INSERT INTO $1 (email, name, surname) VALUES ($2, $3, $4)');

 // Execute the prepared query.  Note that it is not necessary to escape

  foreach ($csvfile as $data)
  {
    $email = $data['email'];
    $name = $data['name'];
    $surname = $data['surname'];
    $query = "";
    $result[] = pg_execute($dbconn, "my_query", array($tbname, $email, $name, $surname));
   }
  if (in_array(false, $result) )
     return false;
  else
     return true;
}
$dbname = "your dbname";
$tbname = "name of table";
$csvFile = [];
if (insertData($dbname, $tbname, $csvFile))
  echo "Data inserted";
else
   echo "Data not inserted";

score 1 · Answer 3 · answered Feb 12 '19 at 14:48

You need to escape the string parameters. And it is much better if you can use PDO extension, because prepared statements can take care of escaping for you and also helps with preventing SQL injection and some other security concerns.

function insertData(PDO $dbh, $csvfile) {
    $x = 0;
    foreach ($csvfile as $data)
    {
        $query = "INSERT INTO users (email, name, surname) VALUES (?, ?, ?)";
        $params = [
             $csvfile[$x]['email'],
             $csvfile[$x]['name'],
             $csvfile[$x]['surname']
        ];
        $statement = $pdo->prepare($query);
        $statement->execute();
        $x++;
    }
}

Thanks for the suggestion as to using PDO; I hadn't used PostgreSQL before writing this small app, and im under a tight deadline & don't have time to change the rest of the app to use PDO at this stage. — BlissSol, Feb 12 '19 at 14:51

score 0 · Answer 4 · answered Feb 12 '19 at 14:54

So i took note of the suggestions from @Karsten Koop and @TOH19, and came up with this code which is working;

function insertData($pg, $csvfile)
      {
        $x = 0;
        foreach ($csvfile as $data)
        {
          $email = pg_escape_string($csvfile[$x]['email']);
          $name = pg_escape_string($csvfile[$x]['name']);
          $surname = pg_escape_string($csvfile[$x]['surname']);
          $query = "INSERT INTO users (email, name, surname) VALUES ('".$email."', '".$name."', '".$surname."')";
          $result = pg_query($pg, $query);
          $x++;
        }
      }

PHP Insert into PostgreSQL

4 Answers4