CODEWITHSUNDEEP
sqlpostgresqlgo
Shubham Chaudhary
Shubham Chaudhary

Reputation: 1482

Create PostgreSQL table dynamically in Golang

I am using "database/sql" package in GO. I want to create a table with a dynamic name.

The only way I can think of is-

db.Exec(`CREATE TABLE`+table_name+`;`)

But it is not safe as there can be SQL injection.
Is there a better way to achieve this?

Upvotes: 4

Views: 11848

Answers (4)

Deepak Puthraya
Deepak Puthraya

Reputation: 1435

We could use QuoteIdentifier

db.Exec(fmt.Sprintf("CREATE TABLE %s", pq.QuoteIdentifier(table)))

Here are the lines from the documentation -

QuoteIdentifier quotes an "identifier" (e.g. a table or a column name) to be
used as part of an SQL statement.  
For example:

    tblname := "my_table"
    data := "my_data"
    quoted := pq.QuoteIdentifier(tblname)
    err := db.Exec(fmt.Sprintf("INSERT INTO %s VALUES ($1)", quoted), data)

Any double quotes in name will be escaped. 
The quoted identifier will be case sensitive when used in a query.  
If the input string contains a zero byte, the result will be truncated immediately before it.

Upvotes: 0

leninhasda
leninhasda

Reputation: 1740

Its just like @Vao Tsun said:

stmt, err := db.Prepare("CREATE TABLE $1")
if err != nil {
    log.Fatal(err)
}
defer stmt.Close()

result, err := stmt.Exec("DB_NAME_HERE")

Go through the original documentation and look at their example as well for clear understanding.

Upvotes: -1

bayrinat
bayrinat

Reputation: 1588

Just use placeholders like:

db.Exec("CREATE TABLE $1", "table_name")

wikipedia:

With most development platforms, parameterized statements that work with parameters can be used (sometimes called placeholders or bind variables) instead of embedding user input in the statement. A placeholder can only store a value of the given type and not an arbitrary SQL fragment. Hence the SQL injection would simply be treated as a strange (and probably invalid) parameter value.

Upvotes: -1

Vao Tsun
Vao Tsun

Reputation: 51649

I don't code in GO, but this would probably be safe for injection:

tx.Prepare(`do $$ begin execute format($f$create table %I()$f$,$1); end; $$;`)

and then 

stmt.Exec(table_name)

Upvotes: 5

Related Questions