CODEWITHSUNDEEP
phpstringpostgresql
BlissSol
BlissSol

Reputation: 418

PHP Insert into PostgreSQL

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 ('[email protected]', 'John', 'O'hare')";

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

Upvotes: 1

Views: 3436

Answers (4)

Faramarz Salehpour
Faramarz Salehpour

Reputation: 601

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($params);
        $x++;
    }
}

Upvotes: 2

toh19
toh19

Reputation: 1229

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;
      }
    }
}

Upvotes: 3

Pascal Tovohery
Pascal Tovohery

Reputation: 986

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";

Upvotes: 1

BlissSol
BlissSol

Reputation: 418

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++;
        }
      }

Upvotes: 0

Related Questions