Search Unity

  1. Megacity Metro Demo now available. Download now.
    Dismiss Notice
  2. Unity support for visionOS is now available. Learn more in our blog post.
    Dismiss Notice

PhpMyAdmin mySQL query format confusion

Discussion in 'Multiplayer' started by Thimble2600, Jan 30, 2019.

  1. Thimble2600

    Thimble2600

    Joined:
    Nov 27, 2015
    Posts:
    165
    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.

    Code (CSharp):
    1.             string recordedPassword = SQLReader.RunQuery (
    2.                 "SELECT " +
    3.                 "Password " +
    4.                 "FROM " +
    5.                 "players " +
    6.                 "WHERE " +
    7.                 "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. Antypodish

    Antypodish

    Joined:
    Apr 29, 2014
    Posts:
    10,753
    This isn't C# problem. Refer to php or MySQL documentation.
     
    Joe-Censored likes this.
  3. Thimble2600

    Thimble2600

    Joined:
    Nov 27, 2015
    Posts:
    165
    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.

    Code (CSharp):
    1.  
    2. public static string RunQuery(string query, int i)
    3.         {
    4.             connection.Open ( );
    5.             try
    6.             {
    7.                 MySqlCommand cmd;
    8.                 cmd = connection.CreateCommand ( );
    9.                 cmd.CommandText = query;
    10.                 MySqlDataReader myReader = cmd.ExecuteReader ( );
    11.                 if ( myReader.HasRows )
    12.                 {
    13.                     while ( myReader.Read ( ) )
    14.                     {
    15.                         Console.WriteLine ( "{0}\t{1}\t{2}\t{3}" , myReader.GetInt32 ( 0 ) , myReader.GetString ( 1 ) , myReader.GetString ( 2 ) , myReader.GetByte ( 3 ) );
    16.                         return myReader.GetString ( i ); //intentionally return on first iteration
    17.                     }
    18.                 }
    19.                 else
    20.                 {
    21.                     return "No applicable results";
    22.                 }
    23.             }
    24.             catch ( Exception e )
    25.             {
    26.                 Console.WriteLine ( "Query error: " + e.Message );
    27.             }
    28.             finally
    29.             {
    30.                 if ( connection.State == System.Data.ConnectionState.Open )
    31.                 {
    32.                     connection.Close ( );
    33.                 }
    34.             }
    35.             return "-1";
    36.         }
    37.  
     
  4. Antypodish

    Antypodish

    Joined:
    Apr 29, 2014
    Posts:
    10,753
    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. maozao

    maozao

    Joined:
    Jan 20, 2016
    Posts:
    12
    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?