Golang and Postgresql CREATE TABLE giving me problems

Question

Using and following the documentation: https://godoc.org/github.com/lib/pq but can't see after hours and hours and research online to find any good example of passing variables to the db.Exec() I'm building a program that will create new tables depending on certain names entered on the command arguments.

db.Exec(`CREATE TABLE $1(
    ID INT PRIMARY KEY NOT NULL,
    HOST    TEXT    NOT NULL,
    PORTS   TEXT,
    BANNERS TEXT,
    JAVASCRIPT TEXT,
    HEADERS TEXT,
    COMMENTS TEXT,
    ROBOTS  TEXT,
    EMAILS  TEXT,
    CMS     TEXT,
    URLS    TEXT,
    BUSTIN  TEXT,
    VULN    TEXT
    )`,  tablename)

But no luck, I obviously have try to change things around, even I have try to build the CREATE TABLE syntax on a string and have try to pass that instead of db.Exec(string) but no luck neither... can someone give me a hand?

Thanks

What the problems? What is exactly not working? Is there any errors? — Roman Kiselenko
– Roman Kiselenko, Commented Sep 13, 2018 at 6:08
yes, as on this example: panic: pq: syntax error at or near "$1" but of course when I change it I get other errors. — cfernandezlinux
– cfernandezlinux, Commented Sep 13, 2018 at 6:16
I don't know GO, but in most other languages I know you can't parametrize object identifiers, only values. You need to concatenate the tablename into the string you pass to the Exec() method — user330315
– user330315, Commented Sep 13, 2018 at 6:16
ohh @a_horse_with_no_name that worked! but.... from a security point. wont it be easy now to just inject SQL code? — cfernandezlinux
– cfernandezlinux, Commented Sep 13, 2018 at 6:21
From a security point of view the application code shouldn't create tables to begin with. Let alone tables where the user specifies the name. — user330315
– user330315, Commented Sep 13, 2018 at 6:24

nouse · Accepted Answer · 2018-09-14 03:12:58Z

2

You can check on https://golang.org/src/database/sql/sql.go?s=39599:39668#L1437, at line 1478, that sql statements will be first prepared then executed.

In PostgreSQL, prepare are only valid for SELECT, INSERT, UPDATE, DELETE, or VALUES, https://www.postgresql.org/docs/10/static/sql-prepare.html .

Here you can use Go's fmt.Sprintf to support creating different tables, and check table name manually, SQL table names can contain many special characters, but you can narrow it, mine validation is regexp.MustCompile("^[a-zA-Z_]+[0-9a-zA-Z_]*$") .

answered Sep 14, 2018 at 3:12

nouse

762 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Golang and Postgresql CREATE TABLE giving me problems

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related