[Resolved] SQL Insert command trouble

Edit I fixed this issue by adding spaces between around parameters.

Working on a project that keeps information on an SQL server, information like player positions, login details, etc.

I'm not all that well versed in SQL but I managed to put together a nice script that selects and returns elements from rows, and that works fine. I'm having trouble with insertions though, when creating new accounts (seen at the bottom of

HandleAccountCreateQuery(...)

.

The error I'm getting suggests the command is wrong, but I've looked online and every example I've found seems to suggest otherwise.

Code (CSharp):

1.  
2.         private static void HandleAccountCreateQuery( int index, byte[] data )
3.         {
4.             //Network stuff...
5.  
6.             //Check if account already exists
7.             SQLReader.Parameter[] parameters = new SQLReader.Parameter[1];
8.             parameters[0] = new SQLReader.Parameter( "?username", username );
9.  
10.             object[] result = SQLReader.RunQuery( @"SELECT * FROM Players WHERE Username = ?username ;"
11.                                                    , SQLReader.Stream.OUTPUT
12.                                                    , parameters
13.                                                    , "ID"
14.                                                    );
15.  
16.             // If result is not null, suggesting player with this name was found, return.
17.             if ( result != null )
18.             {
19.                 ServerTCP.SendAccountCreateFail( index, Log.DatabaseAccountAlreadyExists );
20.                 return;
21.             }
22.             // else no player with this name was found and so we can create the account...
23.             parameters = new SQLReader.Parameter[2];
24.             parameters[0] = new SQLReader.Parameter( "?username", username );
25.             parameters[1] = new SQLReader.Parameter( "?password", password );
26.  
27.             SQLReader.RunQuery( "INSERT INTO players(username,password) VALUES(?username,?password)", SQLReader.Stream.INPUT, parameters );
28.         }

Code (CSharp):

1.         public static object [ ] RunQuery( string sqlCommand , Stream stream , Parameter [ ] parameters = null , params string [ ] columns )
2.         {
3.             object [ ] results = null;
4.  
5.             try
6.             {
7.                 connection.Open ( );
8.  
9.                 // Set the query command
10.                 MySqlCommand cmd = connection.CreateCommand ( );
11.                 cmd.CommandText = sqlCommand;
12.  
13.                 // Add parameters where applicable
14.                 if ( parameters != null )
15.                     foreach ( Parameter p in parameters )
16.                         cmd.Parameters.AddWithValue ( p.parameterName , p.value );
17.  
18.                 // Run the query and yield results
19.                 MySqlDataReader myReader = cmd.ExecuteReader ( );
20.  
21.                 if ( stream == Stream.OUTPUT )
22.                 {
23.                     // If results have rows...
24.                     if ( myReader.HasRows )
25.                     {
26.                         // Read the first row
27.                         myReader.Read ( );
28.  
29.                         results = new object [ columns.Length ];
30.  
31.                         for ( int i = 0; i < results.Length; i++ )
32.                         {
33.                             // Get the field values for each of the specified column names
34.                             results [ i ] = myReader [ columns [ i ] ];
35.                         }
36.                     }
37.                 }
38.             }
39.             catch ( Exception e )
40.             {
41.                 Console.WriteLine ( "[SQLReader] " + "Query error: " + e.Message );
42.             }
43.             finally
44.             {
45.                 if ( connection.State == System.Data.ConnectionState.Open )
46.                 {
47.                     connection.Close ( );
48.                 }
49.             }
50.  
51.             return results;
52.         }

 

You're doing this from the client game? What if you have 10,000 users? Your SQL will likely fall over with far many fewer user connections. Instead, use a web service that talks to your db over a single connection. Also, you are inviting SQL injection with your approach, someone could easily add a semicolon followed by "truncate table...". You are also exposing your username and password.

 

By web service you mean host it on some website? I'm really just looking to learn how to do it on my home computer, at least to start. I doubt any game I ever make will get more than 10 people playing it at a time.
I'll look in to security the database and what-not later. When players create a character it alerts them their details aren't secure and not to use any important passwords and I'm really only testing with a couple people.

All that said I'd welcome any learning resources you think I'd benefit from.

 

You can create a web service on your local computer, for practice and development. Google for tips, "web service to connect to database", "how do I use a REST API to connect to my database" or similar. Regarding the estimated 10 people for example, what DB server are you currently planning to have them connect to? They won't be able to connect to the database on your computer. And each user doesn't each need one, that would defeat the purpose of a database. You could use PlayerPrefs instead, or just local file storage without the overhead of a database connection. You might not want to wait for the security and what not later, you'll likely throw away a lot of time, code and effort in doing so. But understood, taking small steps is good too.

 

All interaction with the database is currently done on the server so when a player moves around in game they send the command to the server which updates the database. And the server is my home computer. I don't have any money to invest into a host sadly

 

My point is, your users are not going to be able to access your home computer. The only person that will be able to run your game, is you.

 

Oh man, well there must be something I've not told you that'd make you think thjcause I've had a few friends test it and they managed to create accounts, login and run around.
Edit: OH YEAH, totally didn't mention the client and server use network sockets.

 

So you have your home computer open to the Internet? Probably not the best idea, if so. Unless you have a proper firewall, etc. Are your friends running from their home PC's, or from mobile devices? Your db connection string would tell the story. How did they install your game?

 

I just sent them a build. And yeah, I'm quite troubled having my computer open like this.

 

You could probably get a free tier host on AWS, that's how I started. You could put your DB and your webservice on the same system. At least your db to start, so your home system is still safe.