pg_query INSERT INTO not working

Question

I have a setup With Apache24, php environment and postgresql database. I'm trying to populate some columns (not all) in db table with values, but it is not working as I would expect.

I get the following error/warning and db is not populated: Warning: pg_query(): Query failed: ERROR: syntax error at or near "c329a92850f6d539dd376f4816ee2764517da5e0235514af433164480d7a" LINE 1: ...word, salt) VALUES (DEFAULT, cff@jjj.no, per, 8254c329a92850... ^ in C:\Users\Public\Server\Apache24\htdocs\eMe\newuser.php on line 34

Any support on this is highly appreciated. I have searched for similar questions but not been able to interpret the answers into my context.

<?php
# Registration form input to postgresql user table in myDB
session_start();

# Retrieve data from input form
$username = $_POST['username'];
$email = $_POST['email'];
$password = $_POST['password'];

# Concatenate and hash password with salt
require_once(dirname(__DIR__).'\eMe\saltgenerator.php');
global $randString;
#$randString = pg_escape_string($randString);
$isalt = pg_escape_string($randString);

$saltandpassword = $isalt. $password;
$hashedpassword = hash('sha256', $saltandpassword, false);

$username = pg_escape_string($username);
$email = pg_escape_string($email);
$hashedpassword= pg_escape_string($hashedpassword);

# Insert data into Postgresql database
# INSERT INTO table_name (column1, column2, column3 .....) VALUES (value1, value2, value3....) 
include_once(dirname(__DIR__).'\eMe\config.php');
$query = "INSERT INTO users (userid, mailaddress, username, userpassword, salt) VALUES (DEFAULT, $email, $username, $hashedpassword, $isalt)";
#$result = 
#pg_query_params($query);
pg_query($query);
?>

I have tried to include quotes and backtick quotes as described on this link but it does not solve the problem. The error/warning is slightly different though:

Warning: pg_query(): Query failed: ERROR: syntax error at or near "`" LINE 1: INSERT INTO users (userid, `mailaddress`, `username`, `use... ^ in C:\Users\Public\Server\Apache24\htdocs\eMe\newuser.php on line 30

Possible duplicate of [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — aynber, Oct 20 '17 at 16:06
I'd really recommend switching to PDO, which can interact with pgsql. With prepared statements and parameter binding, you'll never have to worry about quoting issues, and you won't need to escape your values. — aynber, Oct 20 '17 at 16:08
Thanks for the advice. Ill see if I manage to use PDO. I find it a bit difficult to understand the syntax/setup — om78, Oct 20 '17 at 16:46

score 0 · Accepted Answer · answered Oct 21 '17 at 16:26

The only problem in your code is you didn't figure out yet that when writing that:

$username = pg_escape_string($username);

$username gets escaped for injection, which is good, but this will add necessary quotes inside the value, not around the value (see Whats does pg_escape_string exactly do? for more).

So in the query, quotes are needed around the literal text values in addition to escaping the contents, as in the following:

$query = "INSERT INTO users (userid, mailaddress, username, userpassword, salt)
          VALUES (DEFAULT, '$email', '$username', '$hashedpassword', '$isalt')";

(given that the variables in this query have already been passed through pg_escape_string)

Hi thanks for your help, and sorry for late response. However, I switched to using PDO API instead by following some examples and made it work that way. — om78, Feb 09 '18 at 11:13

pg_query INSERT INTO not working

1 Answers1