Getting Driver Bad Connection Error When using Postgres Driver for Go

[Rhonda Reece]

I am new to go and I am trying to use the Go SQL driver for Postgres to execute a stored procedure in postgres by using the .Exec method. When I execute my code I get the error: driver bad connection exit status 1. Below is my go code I am executing. I noticed there is not a lot documentation on running stored procedures through the driver. If someone can assist me on my error, I will greatly appreciate it. I am running Postgres on a linux server and the stored procedures are associated with the database.

package main

import (

"database/sql"

"fmt"

_ "github.com/lib/pq"

"log"

)

//Variables for Create User Account

var (

userUserName  string

userPassword  string

userFirstName string

userLastName  string

userEmail     string

status        string

rating        int

)

func addUserAccountDB(userFirstName string, userLastName string, userEmail string, userUserName string, userPassword string) {

//opening a db connection

db, err := sql.Open("postgres", "user=postgres dbname=meritwiki password =go sslmode=disable")

if err != nil {

log.Fatal(err)

fmt.Println("there are errors")

} else {

fmt.Println("no errors")

}

//executing the stored procedure called addUserAccountDB that accepts the values of userFirstName, userLastName, userEmail, userUserName, and userPassword

chkDB, err := db.Exec("select addUserAccountDB ('" + userFirstName + "','" + userLastName + "','" + userEmail + "','" + userUserName + "','" + userPassword + "')")

chkDB = chkDB

if err != nil {

log.Fatal(err)

fmt.Println("there are errors for chkDB")

//chkDB = true

} else {

fmt.Println("no errors for chkDB")

//chkDB = false

}

return

}

//setting the declared variables to test that the function works

func main() {

userFirstName := "Frog"

userLastName := "Jones"

userEmail := "frog...@gmail.com"

userUserName := "frogjones"

userPassword := "testGo"

addUserAccountDB(userFirstName, userLastName, userEmail, userUserName, userPassword)

}

[Jason Del Ponte]

Does your code execute successfully just connecting to the database without doing a query?

[Alex Skinner]

Agreed.  Can you identify exactly where the error is occuring in the provided code?  Further, can you provide some version of the stored proc so that we may test?

[Tamás Gulácsi]

Please NEVER use such string manipulation for query creation, but use only prepared/parametrized queries!

[Nigel Vickers]

Sorry, but I don't quite understand. Why should one not be able to "build" a parameter list in this way ?  Stored proceedures check their own constraints or is the driver imposing constaints?

[Marcus Holmes]

because you're building a SQL statement from user input, it's vulnerable to SQL injection.

e.g. what happens if your user first name is "'','','','','');Drop table Users;"?

Your query will compile happily, your stored procedure will complain about five blank parameters, and your user table will be dropped.

[Marcus Holmes]

Obligatory XKCD reference: http://xkcd.com/327/

[Nigel Vickers]

Where or When sanitisation takes place is dependant on many factors. How the stored procedure evaluates it's parameter list is dependent on Database /Procedure Language and function(). Most stored procedure "validators" treat a parameter list as simple text. Much time and effort has been spent creating extensive Rights Management at Database Level. For a "smuggled" "drop TABLE" to be effective requires a number of failures.  Without seeing the code of the "stored proceedure" your admonition is premature. 

[Nigel Vickers]

Is the cartoonist laughing at unsanitised inputs or the DBA allowing the application inappropiate rights on the database? 

[Marcus Holmes]

But there's a really simple way of avoiding problems: don't build ad-hoc sql statements in code, instead use parameters. I agree it's one layer of many, but ignoring it is foolish.

[Gyepi SAM]

On Fri, Mar 21, 2014 at 12:34:34AM -0700, Nigel Vickers wrote:
> Where or When sanitisation takes place is dependant on many factors. How
> the stored procedure evaluates it's parameter list is dependent on Database
> /Procedure Language and function(). Most stored procedure "validators"
> treat a parameter list as simple text. Much time and effort has been spent
> creating extensive Rights Management at Database Level. For a "smuggled"
> "drop TABLE" to be effective requires a number of failures. Without seeing
> the code of the "stored proceedure" your admonition is premature.

I disagree. Security does not depend solely on any one system component.
Yes, the database may have security controls, and that is good, but programmers
should also not knowingly send unsanitized input, even to a stored procedure.
One who does not realize this needs to find out quickly.

The use of query parameters is a well established protocol for
talking to SQL databases. Experienced programmers almost never build query
strings manually (not simply for security reasons; readability,
maintainability and understanding count too), but inexperienced ones frequently
do. However, inexperience in this case could be dangerous; the warning is
warranted, even without seeing the stored procedure.

-Gyepi

[Nigel Vickers]

Sorry we hijacked the thread...

Here is one of our functions that might help with using parameters(and reduce the noise), but until we have the stored procedure we can't outguess the driver.

//write userdata to login attempts----------------------------------------------------------

func LoginTrys(user, ip_address string) int {

mydb, err := sql.Open("postgres", connectString)

if err != nil {

if debug {

fmt.Println("sql.Open failed", err)

}

}

defer mydb.Close()

        //prepare the statement

stmt, err := mydb.Prepare("INSERT INTO login_attempts (ip_address, login, ctime ) Values($1,$2,$3)")

if err != nil {

fmt.Println("write login error:", err)

}

ctime := time.Now()

        // execute it

res, err := stmt.Exec(ip_address, user, ctime)

fmt.Println("write Try:", res, err)

// an alternative on a single row return

        var trys int

err = mydb.QueryRow("select count(*) from (select login from login_attempts where login = $1) as rows ", user).Scan(&trys)

if err != nil {

fmt.Println("Get trys err", err)

}

return trys

}

 hope it helps!

http://go-database-sql.org/ has some good nonspecifics.

[Rhonda B. Reece]

Hi Jason,

Yes the code open's a database connection without errors but the error comes when trying to do the db.exec command.

On Thu, Mar 20, 2014 at 10:14 PM, Jason Del Ponte <delp...@gmail.com> wrote:

Does your code execute successfully just connecting to the database without doing a query?

--
You received this message because you are subscribed to a topic in the Google Groups "golang-nuts" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/golang-nuts/MS2VW9aFXZs/unsubscribe.
To unsubscribe from this group and all its topics, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Ignite Peace!

Rhonda B. Reece
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Email: rhond...@gmail.com  Website: www.rhondareece.com 

Social Media: Linkedin   Facebook   Twitter  SoundCloud

Blogs: TripleBAR
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Inspirational Quotes:

"Being grateful is by far the single most powerful thing you can do to change your life. When you allow yourself to feel gratitude, wherever you are, for whatever is going on in the present moment, in the now, what I promise you is that the spiritual dimension of your life will change. It will open up and it will expand and you will grow with it. The more you are grateful for, the more comes for you to be grateful for. If you want to fulfill your desire and your destiny, start by being grateful. When we say thank you for what we have, we acknowledge all that the universe has given us and then you’ll 

begin to see…more shows up. " (By: Oprah Winfrey, 2013)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

"Give out of love and not out of expectations." (By: Rhonda B. Reece. 2013)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

"When our goal is to honor the Lord, He guides and guards us each step of the way, Whatever our hopes and dreams may be, when we place them in God's hands we know that everything, including setback or success, is under His 

control." (By David McCasland)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

"Sometimes opportunities that come our way are new doors opening up to us. But, if we don't walk through the door, 

the path can't be traveled." (By: Rhonda B. Reece, 2012) 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

"Discipline is the bridge between goals and accomplishment" (By: Jim Rohn)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

"The road to drive is the one your heart takes you." (By: Rhonda B. Reece, 2011) 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

“The celebration of small wins is faith’s exultation for great wins to come.” (By: Rhonda B. Reece, 2011) 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

"Sometimes, dollars just don't do it but it is the longevity of happiness and success of implementing the dream

 which will grant great prosperity.” (By:  Rhonda B. Reece, 2011) 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

"The minute people stop judging and start encouraging one another is the day we get closer to a place of

 peace in society!" (By Rhonda B. Reece, 2011)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

"When people show themselves to you once, it is the truth. Always pay attention to the signs and your feelings because you can save yourself from a lot of hurt and pain." (By: Rhonda B. Reece, 2010) 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

"Faith is a journey not a destination" (By: Unknown) 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

"Voy a perseverar en la verdad y aniquilar a paleros."  "I will persevere in the truth and destroy drainers" 

(By: Rhonda B. Reece, 2010) 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

"Change your mindset so that your thinking is in line with your purpose and then with faith MOVE!" 

(By: Rhonda  B. Reece, 2009)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

"My deepest fear is not that I am inadequate but that my success will catapult me beyond my purpose into a sea 

of infinite opportunities and resources, which makes me powerful beyond measure." By Rhonda B. Reece, 2008 

(Inspired by Marian Williamson's Quote)

[Rhonda B. Reece]

Hi Alex,

Below is the stored procedure that I want to execute

CREATE FUNCTION addUserAccountDB(userFirstName character, userLastName character, userEmail character, userUserName character, userPassword character)

  RETURNS text AS

$BODY$

BEGIN

If NOT EXISTS (select "Email" from public."MW_User" where "Email" = userEmail) THEN

INSERT INTO "MW_User" ("First_Name", "Last_Name", "Email", "UserName", "Password") 

VALUES (userFirstName, userLastName, userEmail, userUserName, userPassword);

return 'The user info was added. The userName is ' || userUserName;

ELSE

  return 'The email ' || userEmail || ' exists';

END IF;

-- end begin

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

--

[Rhonda B. Reece]

Thank you Nigel for your assistance. I understand this is a very vanilla approach, I am new to golang and wanted to get postgres to be able to use the go sql driver for postgres correctly before I start refining the function. I appreciate all of the feedback thus far.

[Andy Balholm]

On Friday, March 21, 2014 11:43:02 AM UTC-7, Rhonda Reece wrote:

Hi Jason,

Yes the code open's a database connection without errors but the error comes when trying to do the db.exec command.

Because of the way the connection pooling works in database/sql, opening the first actual connection to the database is deferred until the first query is executed. 

[Matt Silverlock]

http://golang.org/pkg/database/sql/#DB.Ping 

err := db.Ping() and see if it's accepting connections. The connection string/initialisation prepares a pool, but won't connect to the DB until you execute a query (as touched on above).

[Nigel Vickers]

Hallo Rhonda,

I got a chance to have a detailed look at what your trying to do. Your "bad connection " may result from a missing "host=" parameter in your connect string. Your call to db.Exec() returns an sql.Result which won't contain the returns from your stored procedure. Your stored procedure conditional should require a return in "if then" and "else".

Given this table:

create table teststate

(

statenr serial NOT NULL,

statename text,

stateresponse text

)

and this stored procedure:

 create or replace function addrow(sname text, sresponse text )

  returns text as

  $body$

 

  begin

  if not exists(select statename from teststate where statename = sname )then

  insert into teststate (statename,stateresponse)

  values(sname,sresponse);

  return 'Inserted';

  else

  return (select stateresponse from teststate where statename = sname);

  end if;

  end;

$body$

language plpgsql volatile

This go program:

package main

import (

"database/sql"

"fmt"

_ "github.com/lib/pq"

)

const connectString string = "user=postgres password=mypassword dbname=mydb sslmode=disable host=localhost"

func main() {

param1 := "first"

param2 := "I am One"

mydb, err := sql.Open("postgres", connectString)

if err != nil {

fmt.Println("sql.Open failed", err)

}

defer mydb.Close()

call := "select addrow($1, $2)"

response := ""

err = mydb.QueryRow(call, param1, param2).Scan(&response)

fmt.Println("response/err first call", response, err)

}

will return

nigel@fossil:~/go/src/testsp$ go run testsp.go

response/err first call Inserted <nil>

the first time, and

nigel@fossil:~/go/src/testsp$ go run testsp.go

response/err first call I am One <nil>

the second time. Which I think is what you wish to achieve.

rgds, Nigel

On Friday, 21 March 2014 01:43:17 UTC+1, Rhonda Reece wrote:

[alb...@ecsagency.com]

I'm trying to connect my application with postgres. I followed the instructions in :https://libraries.io/go/github.com%2Flib%2Fpq. I tested in the terminal and shows ok github.com/lib/pg.
but when I run my application says can't find import: "github.com/lib/pq"
someone can help me please.

my application looks like:
import (
"database/sql"

_ "github.com/lib/pq"

)

[Tamás Gulácsi]

Have you go getted that lib? Is it at $GOPATH/src/github.com/lib/pq ?