Mysql statement problem

Getting Help
1

I have the following code:

if queryuser, err = db.Prepare("select UserID, CurrentDB from optUsers where LoginHash = '?'"); err != nil  {
  println(err)
  panic( "queryuser Statement failed to create" )
}   

var1 := req.URL.Query().Get("hash")
row := queryuser.QueryRow( var1 )
var userinfo user
row.Scan(&userinfo.UserID, &userinfo.CurrentDB)

The problem is if I use a statement to execute my query I get back no results. But if I use a db.QueryRow() and just execute the query inline I get back my results. Does anyone see anything wrong with my statement code?

thanks,

Glenn

1 Like
2

Hey Glen! I ran into some trouble with MySql & the db.prepare statement as well. You can take a look at my code to see how I worked around it (Im fairly new with Go & development in general, so the code needs to be cleaned up quite a bit. However, you can see how I executed my queries)

3

Iā€™m not sure if I understand what your problem is?

If by ā€œexecutingā€ the statement you mean to call Exec() on the statement, then yes, you do not get any database items back, as Exec() is meant for write operations.

If you want to read, then you use QueryRow() and iterate over the result set until it is empty.

4

row.Scan also returns an error. Catch this and check what it says.

5

NobbZ, where do you see me doing an exec? Iā€™m doing a queryrow and not getting results back. But if I switch to regular db.QueryRow without using the statement it works just fine.

6

I do not, but you said you want to ā€œexecuteā€ the query, and I asked if Exec was what you meant, which as it seems you didnā€™t.

Do you see any error messages? Have you checked the returned error value of your function calls?

7

First off Iā€™m surprised by the fact that Scan returns error and didnā€™t make me catch it. I thought you had to put _ any time you didnā€™t want to use a return var. So I had no idea this function returned anything.

Secondly, the return Iā€™m getting says ā€œsql: expected 0 arguments, got 1ā€ But in my statement I created it has 1 argument so Iā€™m still not sure what is wrong. Am I passing the var in wrong or something?

thanks,

8

I might be wrong, but doesn '?' in the query mean a string containing a literal question mark? Iā€™d omit the quotes.

9

Try writing ? instead of ā€˜?ā€™. I think the apostrofs make it count as just a string.

10

Dennis,

Youā€™re not using statements but instead using what I stated works for me as well which is to just run the query directly. However, I wanted to point out a few things you might want to consider.

  1. You are creating your db connection within each function. From what Iā€™ve been told you really only need one db connection created in main and then used as a global variable in the other functions. This will speed things up dramatically.

  2. I think you might be using defer too late. The reason for having a defer statement is to make sure something happens no matter what. Closing the db connection is your example but what happens if your code dies before it gets down to the bottom of the function to close the connection. So instead what you want to do is create the connection and immediately tell it to defer close. Then if something happens before it gets to the bottom it will still close the connection for you. The way youā€™re using it you might as well just replace it with a close and remove the defer.

11

Ok, so that got rid of that error now I get ā€œno rows in result setā€ But again, if I run this with db.QueryRow it works perfectly. So it is something with the statement that it thinks nothing was found.

Also, do I need to be doing anything about sql injection here? I am not sure if things are getting slashed out by default or if I need to be running through other measures to insure no one can mess with my queries.

Thanks,

Glenn

12

Iā€™m also assuming it is adding the ticks for strings and stuff automatically? Since removing the ticks did away with the error but Iā€™m passing in a string for that parameter that definitely needs one for a proper query.

I also donā€™t seem to have an error response from the QueryRow itself so not totally sure it is executing properly or not.

13

Can you please provide an example of both ways you do it?

Also telling us how your table looks like even with sample data helps a lot.

Of course, you want to minify the example table and avoid complicated queries or even joins.

14

table is long but only looking at two fields here
UserID int unsigned not null auto_increment
CurrentDB char(50)
LoginHash char(50)

userinfo is a struct with two fields:

type user struct {
UserID int              `json:"userid"`
CurrentDB NullString    `json:"currentdb"`
}

Code that works:

var1 := req.URL.Query().Get("hash")
query := "select UserID, CurrentDB from optUsers where LoginHash='" + var1 + "'"
row := db.QueryRow( query );
row.Scan(&userinfo.UserID, &userinfo.CurrentDB)

Code that doesnā€™t work:

queryuser, err = db.Prepare("select UserID, CurrentDB from optUsers where LoginHash = ?")
var1 := req.URL.Query().Get("hash")
row := queryuser.QueryRow( var1 )
row.Scan(&userinfo.UserID, &userinfo.CurrentDB)

There is no real purpose in this call btw: Just trying to write a simple test in go to get my head wrapped around all the processes. Iā€™m considering writing a huge PHP application in go in the future and just using this program to work out the details.

15

You shouldnā€™t do this! This is vulnerable to SQL injections.

Have you inspected the actual value of var1, maybe it is not exactly what you expect it to be? Have you tried hardcoding it to a value you know it does exist in the database? Have you tried the sugared version of prepared statements? db.Query("select UserID, CurrentDB from optUsers where LoginHash = ?", var1)

I can not currently set up a testprogram on my own, as this computer is rather limited in its ressources and installing more would probably spill the disk :wink:

16

Glenn,
Thank you so much! Iā€™m planning to restructure The code tonight So that the database is a global variable . I appreciate your feedback

Dennis

17

I had multiple problems and still looking into the last one. Originally I was using the default http processes and recently changed to gorilla mux to try it out. In the process I changed my var1 to use
vars := mux.Vars( req ) as it said it would put my vars into a map but apparently accessing vars[ā€œhashā€] isnā€™t the correct way to do that. I hard coded my hash in and it works. So my issue related to this thread was answered with removing the ticks from around ?.

However, am I safe in thinking this method is safe to use to protect against sql injection? I know my other example was not which was why I was trying to figure out the statement stuff.

Thanks for you help,

18

Your db is already a global var. So just create it in the main func block and not in each call.

19

mux.Vars() returns variables from the routes, not from the query string.

To get the querystring and its values, you still need to it the way you did before, at least from quickly glancing the mux-docs.

And prepared statements are as safe as prepared statements in the RDBMS you are using are.

Go literally tells your RDBMS this:

GO: Hey, in the near future, someone might ask you about this query: ā€œselect UserID, CurrentDB from optUsers where LoginHash = ?ā€
RDBMS: Okay, Iā€™ll remember that query as ā€œ1ā€
GO: Hey, do you remember Query 1? Here is the missing argument: "deadb33f", its a string.
RDBMS: Okay, here is your result. ā€¦

From a language/DB-driver perspective you can not do much anymore to make it more or less secure against SQL-Injection. This is independent from whether or not you use them in PHP, Go, Rust or any other language.

20

Ah ok. yep, that fixed it. Thanks a bunch.

Glenn