PhpMyAdmin mySQL query format confusion

Unity Engine
1

Probably not the best place to ask this. I’m having some difficulty with mySQL where I pass in queries which cause errors where on other platforms there are no errors.

For example ```SELECT * FROM players WHERE Username = Anthony AND Password = `Anthony’s Password```` yields "Unknown column ‘Anthony’ in ‘where clause’.

It works fine when I use double quotation marks but not single quotes.


In my code however I’m struggling to get quotation marks to work in literal strings, I know I have to add a backwards slash before each quotation mark, but it comes out including the backslash in the string.

            string recordedPassword = SQLReader.RunQuery (
                "SELECT " +
                "Password " +
                "FROM " +
                "players " +
                "WHERE " +
                "Username = ' "  + username + " ' " , 0 );

Outputs "SELECT Password FROM players WHERE Username =\"Anthony\""

Query error: Input string was not in a correct format.

Bah, it’s a big mess. Someone please tell me what I’m doing wrong.

2

This isn’t C# problem. Refer to php or MySQL documentation.

1 Like
3

Right, but I’m not ready to rule out that it’s just a string related issue yet. I’m not using any php files, all the code is written in C#, and I figure mySQL is somewhat related to networking since it involves contacting a server.

public static string RunQuery(string query, int i)
        {
            connection.Open ( );
            try
            {
                MySqlCommand cmd;
                cmd = connection.CreateCommand ( );
                cmd.CommandText = query;
                MySqlDataReader myReader = cmd.ExecuteReader ( );
                if ( myReader.HasRows )
                {
                    while ( myReader.Read ( ) )
                    {
                        Console.WriteLine ( "{0}\t{1}\t{2}\t{3}" , myReader.GetInt32 ( 0 ) , myReader.GetString ( 1 ) , myReader.GetString ( 2 ) , myReader.GetByte ( 3 ) );
                        return myReader.GetString ( i ); //intentionally return on first iteration
                    }
                }
                else
                {
                    return "No applicable results";
                }
            }
            catch ( Exception e )
            {
                Console.WriteLine ( "Query error: " + e.Message );
            }
            finally
            {
                if ( connection.State == System.Data.ConnectionState.Open )
                {
                    connection.Close ( );
                }
            }
            return "-1";
        }
4

I wouldn’t recommend using Unity, to write directly to DB, unless you are 100% that client will not try to hack the application and DB is not connecting to other Unity clients. For example online games. In such case.PhP would be recommended solution, as guarantee server side safety. Providing you write PhP in proper manner.

Either way, try single quotation, depending on SQL server.

5

Try to use simple quotes:

SELECT * FROM players WHERE Username = ‘Anthony’

Edit: Wait, is your problem the query at all, or just the ’ in the “Anthony’s Password” string?