Golang和PostgreSQL的创建表给我的问题

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

Answer 1

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_]*$") .