0

I created a database with my .sql file with this: 

CREATE SEQUENCE ec_account_id 
start 1
  increment 1;

create table ec_account
   (id          serial PRIMARY KEY,
    name        VARCHAR(40) not null,
    password    VARCHAR(80) not null,
    email       VARCHAR(30) not null,
    phone       VARCHAR(10) not null);


insert into ec_account values (nextval('ec_account_id'),'admin','68dc71d4b0724561008d7665a37d9f8bba008f95836c0caab9656d9f1983d314','123456@gmail.com','123456789');
insert into ec_account values (nextval('ec_account_id'),'dsds','cfb68d2dba58568ff9e223235ff1b77b3cb42c371403832a434112aabc','johnnsySilva@gmail.com','123456789');

And i could watch it(the table) via terminal. Check the passwords on the html. Everything was going along fine. But now i want to add new persons to the database via an html form and i want the id to increment automatically, however im not being able to insert (via php) the instances on the database cause when i run this code i get this error:

$sql =  "INSERT into ec_account values ('nextval('ec_account_id')','$nome','$hashedPass','$email','$telemovel') ";

ERROR: ERROR: syntax error at or near "ec_account_id" LINE 1: INSERT into ec_account values ('nextval('ec_account_id')','f...

                                ^

Im almost sure it has something to do with the next val but i dont know how to solve it. Can somebody clarify me? I don't want the responsability of having to memorize how many people are already enrolled in the ec_account table, and i thought this was the way to automatically increment the primary key whenever i insert a new row.

404
  • 8,022
  • 2
  • 27
  • 47
John Doe
  • 59
  • 7
  • 2
    I don't think your `nextval` should be quoted, it doesn't match the DB query. I don't know postgresql though so tis a guess. – user3783243 Nov 18 '18 at 17:28
  • Possible duplicate of [PostgreSQL Autoincrement](https://stackoverflow.com/questions/787722/postgresql-autoincrement) – Sean Nov 18 '18 at 17:35

1 Answers1

1

Seems like you made a fairly simple mistake:

$sql =  "INSERT into ec_account values ('nextval('ec_account_id')','$nome','$hashedPass','$email','$telemovel') ";

Notice your extraneous single quote before nextval. Remove that and the closing one you added and things should be fine.

$sql =  "INSERT into ec_account values (nextval('ec_account_id'),'$nome','$hashedPass','$email','$telemovel') ";

Just to clarify, SQL needs quotes around string values. You do not need to randomly add quotes around every variable you are passing.

Another clarification about Postgresql, is that when you define a column as type "serial" Postgresql creates a sequence for you with the name tablename_columname_seq. So you redundantly created a sequence when you could have used nextval('ec_account_id_seq') and do not need to create the sequence ec_account_id.

When you author an insert statement you should avoid using the shorthand method and explicitly list the columns. This avoids issues later should you need to do an insert that doesn't include all columns, or if you add a column to the table which will break your existing insert statements.

Fixed:

$sql =  "INSERT into ec_account ('id', 'name', 'password', 'email', 'phone') 
values (nextval('ec_account_id'),'$nome','$hashedPass','$email','$telemovel')";

A final word about SQL injections, escaping and parameters:

This technique of using PHP strings to interpolate values is prone to numerous problems and creates vulnerabilities in your system.

I don't know if you are using the pg api or PDO, but in either case, you should be using parameters to send in your values.

Here's a link to the pg_query_params page that explains this.

I'd recommend using PDO personally.

gview
  • 14,876
  • 3
  • 46
  • 51