Search Unity

  1. Click here to see what's on sale for the "Best of Super Sale" on the Asset Store
    Dismiss Notice
  2. Good news ✨ We have more Unite Now videos available for you to watch on-demand! Come check them out and ask our experts any questions!
    Dismiss Notice

Starting my MMO with SQL SERVER! in Javascript

Discussion in 'Multiplayer' started by brust, Feb 10, 2011.

  1. brust

    brust

    Joined:
    Jul 13, 2010
    Posts:
    10
    Hi everyone!, I was looking for a tutorial, who can make me learn SQL on UNITY, so, everyone just scary me!
    Thanks for Tempest, and Xandeck, but i want something more, and more simple, so, i will share with you guys what i've learn, I'm from Brasil, so, my english is from video games! :D,

    Here we are going to learn:

    • Connecting Unity to a Database, with login and Password
    • Making a Search using Select.
    • Have results from the database to use.
    • Use what we have got.
    Starting:

    First, you are going to have a database running on a server, (i don't intend to explain this, cause i don't know, i've got a server from my company to test, later i'll explain, when i learn someday, maybe), but, with a server running, create a new Unity Project, name it whatever you like, and don't import anything, because it's faster this way :D, and then, create a single Javascript file, rename it to whatever you like, and let's start the cool stuff!

    The Headers:

    Type it there!

    Code (csharp):
    1. import System;
    2. import System.Data;
    3. import System.Data.SqlClient;
    Yes! We are using C# libs in Javascript!, we are sooo bad :cool:

    Observations:
    And now, create a Start function to test the code.

    Code (csharp):
    1. function Start () {
    2. [INDENT]//and don't belive i've teaching this! sorry for making you feel like a noob![/INDENT]
    3. }
    The Harder part:

    And now, ladies and gentlemans, the connection comand!

    lets create the main class object of the connection:

    Code (csharp):
    1. var dbcon : IDbConnection;
    you can name dbcon to DATABASE, to be more intuitive.
    and lets do the connection:

    Code (csharp):
    1. var connectionString : String =
    2.     "Server=xxx.xxx.xxx.xxx;" + // put the ip here!
    3.     "Database=INSERT DATABASE NAME HERE;" +
    4.     "User ID=INSERT LOGIN HERE;" +
    5.     "Password=INSERT PASSWORD HERE;";
    6. dbcon = new SqlConnection(connectionString);
    7. dbcon.Open();
    We created a var to store the "url" to the bank, and Instantiate the class for connections.
    Remenber: we are using SQL Server, (in my case, SQL Server 2008 R2), but i'm sure it can work with SQlite, Oracle, or something else, all you need to do for it to work, its learn,
    You can: search for a code that have connection to the base you want, and just look for the Connection String.

    And now, we are going to make a SQL command, the classic, the one, the awesome, SELECT!

    Code (csharp):
    1. //create the class EXECUTIONER, that execute SQL commands
    2. var dbcmd : IDbCommand = dbcon .CreateCommand();
    3. //string var, to save the command we want to use
    4. var cmdSql : String = SELECT [COLOR="blue"]idUsuario[/COLOR], [COLOR="blue"]nmUsuario[/COLOR], [COLOR="blue"]dsLogin[/COLOR], [COLOR="blue"]dsSenha [/COLOR]FROM [COLOR="#ff8c00"]FestaJunina[/COLOR].[COLOR="red"]tbUsuario[/COLOR];
    5. //we add the command, as string, to the executor, to shot it!
    6. dbcmd.CommandText = cmdSql;
    7. //and then, we create a table, like a normal db table, to use it on unity, and we use the function that "plays" the command
    8. var reader : IDataReader = dbcmd.ExecuteReader();
    Subtitle of colors:
    Blue: Columns Names.
    Orange: Main Database name.
    Red: Table name.

    this all that we made till here, did:
    • Conected to the database
    • Send a command
    • Execute a command
    • Returned results

    Now, lets read what we got from the database! :D

    Code (csharp):
    1. while(reader .Read()) {
    2.         var id : String = reader ["[COLOR="#2e8b57"]idUsuario[/COLOR]"].ToString();
    3.         var nome : String = reader ["[COLOR="#2e8b57"]nmUsuario[/COLOR]"].ToString();
    4.         var login : String = reader ["[COLOR="#2e8b57"]dsLogin[/COLOR]"].ToString();
    5.         var senha : String = reader ["[COLOR="#2e8b57"]dsSenha[/COLOR]"].ToString();
    6.         print ("ID: " + id + "NAME: " + nome + "LOGIN: " + login + "PASSWORD: " + senha);
    7.     }
    This 'll make the following thing:
    For every while loop, the table we created to store the table that is the result of our query on the database, the reader jumps to the next line.

    Did you get it? No?
    hahahahahaha, man! i have to go to a english school! Humpf :(

    Ok, "reader " is the name of my table, that holds the data i've got from my search. ok?
    The reader could have n lines, and have 4 columns, and they are: id, name, login, password.
    we need to go on every line, to read it, column by column. (it's column right? or Colunm? or, whatever...)

    Green: Name that i've used to created the table.

    and, after that, use:

    Code (csharp):
    1. reader .Close();
    2. reader = null;
    3. dbcon.Close();
    4. dbcon = null;
    I now you got what this code means.

    This is it!

    In my unity it have returned:

    Code (csharp):
    1. ID: 1 NAME: Daniel LOGIN: daniel@XxXxXx.com.br PASSWORD: 123456
    2. UnityEngine.MonoBehaviour:print(Object)
    3. ConectaSQLSERVER:Start() (at Assets/ConectaSQLSERVER.js:23)
    Hope you got it!
    Thanks everyone, any questions, put it in here.
    I'll show later how to create a database, because, i have to get back to work :eek: my boss its close!

    And God bless you all!
    because He gives me wisdom for that.
     
  2. handsomePATT

    handsomePATT

    Joined:
    Nov 30, 2010
    Posts:
    574
    ive been working on something similar recently and ive heard its not safe for your database to have it connect directly through unity. is this true?
     
  3. brust

    brust

    Joined:
    Jul 13, 2010
    Posts:
    10
    i think that a web page can be hacked, and so a software, but a software is so more dificult, because its compiled!, and you can program something inside a unity client, and a unity dedicated server, so that no one will ever know where the database is, and what is the password,so, i think, if no one else have a argument, this kind of connection, can be suficient safe. =]
     
  4. KEMBL

    KEMBL

    Joined:
    Apr 16, 2009
    Posts:
    181
    Unpack dll in unity standalone build or even webplayer build no problem at all, since Reflector and netobf exists.
    Use database as game server is not very clever Idea because big lack of security. :eek:

    If you plane to make SQL requests from client you need add users directly in to you database with very restricted user role for they. All game logics and checks needs to be placed in to StoredProcedures, Functions, extendet StoredProcedures and etc. All DB operations needs to be processed throug this StoredProcedures/Functions, so data in tables will be on some level of safe. Game performance will be limited by DB performance, which will be not much cause game logics counting is not DB specialised operations. ;)
     
  5. Quietus2

    Quietus2

    Joined:
    Mar 28, 2008
    Posts:
    2,060
    You should read through this thread. As Kembl mentioned, Unity games are not compiled. Extracting the source code for a Unity game is as complicated as clicking one button.

    http://forum.unity3d.com/threads/17117-Hack-resistance

    I believe he's talking about using a Unity dedicated server for his MMO, connecting it to a database on his network. Not stored procedures through ODBC from the client. That would be a lol-fest.
     
  6. handsomePATT

    handsomePATT

    Joined:
    Nov 30, 2010
    Posts:
    574
    but to access the database you have to put your database name server name and password right? so those could easily be extracted.
     
  7. Vinícius Sanctus

    Vinícius Sanctus

    Joined:
    Dec 14, 2009
    Posts:
    282
    Inspite of all the hacking protection issue, thx for sharing your knowlegde Mr. Bruster, its an awesome tutorial and it worked like a charm!

    Way to go friend!

    =)
     
  8. Dreamora

    Dreamora

    Joined:
    Apr 5, 2008
    Posts:
    26,602
    I don't see a problem with using SQL. The login is handled by the servers anyway and that won't end on the end users machine, so security constraints wouldn't exist.

    The larger problem is that unity is very unsuitable as backend solution for the MMO
    You should definitely not use it as its not performance and you can't optimize it there. Also unity is not able to use multithreading so you have to split stuff into different servers just for the sake of targeting different cpu cores by setting their cpu affinity on the process.

    Using unity networking especially is a big no no as you can't have dozens of servers running to have a few thousand players especially if using other, more suited solutions would get you 10-40 times as many players at the same cost and it does not offer server - server connections so you have to use system.net and unity networking on the backend to get server - server communication and alike

    Unity networking etc are targeted at situations where you players host games or you put up dedicated servers and alike with 4 - 128+ players (128+ only if the networking is as light and non action packed as an MMO though and if you opted it)
     
  9. brust

    brust

    Joined:
    Jul 13, 2010
    Posts:
    10
    thanks dreamora! i not so good with unity, as some legends as Quietus, and, i see so many dificults with sql and other stuff, and i'm fisrt worring about making it work, than making it secure!, and i'm acomplishied (sorry for that, i'm from brazil) this! and i'm so excited! and i want to share it with everyone!, but thanks you!
     
  10. Vinícius Sanctus

    Vinícius Sanctus

    Joined:
    Dec 14, 2009
    Posts:
    282
    We lower levels love you Bruster! Thx for all your sharings!

    =)
     
  11. daniballester

    daniballester

    Joined:
    Jan 7, 2011
    Posts:
    2
    I need to develop a webplayer app and I need to access a database to read the position of the objects, is that possible?

    tks,

    dani
     
  12. handsomePATT

    handsomePATT

    Joined:
    Nov 30, 2010
    Posts:
    574
    yes it is
     
  13. brust

    brust

    Joined:
    Jul 13, 2010
    Posts:
    10
    I've got to work a webplayer with a simple Register/Login app,
    using .asp page to connect to the database, its simple, i'm making a tutorial, soon i'll send to you!
     
  14. beltzaser

    beltzaser

    Joined:
    Aug 4, 2010
    Posts:
    16
    Just a warning using SQL Server. It uses page locking, and if you use transactions, you can block SELECT queries using the same page. This could be a nightmare accross a WAN where pings are rather large since one SELECT or UPDATE requires miltiple round trips.

    To prove this, open two SQL Navigator windows, in one, do a begin trans, and update, no commit or rollback yet. In the second one, SELECT from the same table, you will notice the second SELECT hangs, in the first Navigator window, do a commit or rollback, and in an instant you will see the second SELECT executes. The workaround is to do a SELECT with NOLOCK, but then you will get dirty uncommitted data back.

    Design wise its better to send a message to a server to handle updates by sending messages. I dont want people to have a password of my database kept on a client. No matter how little security you give the client account. But again, nothing is unhackable.
     
  15. boxer276

    boxer276

    Joined:
    Mar 28, 2011
    Posts:
    4
    What is the correct route of "System.data.dll"? It has many different copies.
    I can connect to the SQL Server database inside Unity3.0, but cannot connect after building into a .exe file.
    I was told that I'm using a wrong System.data.dll. Anyone can help me?
     
  16. jashan

    jashan

    Joined:
    Mar 9, 2007
    Posts:
    3,250
    I wouldn't directly connect to a database from a Web player or even standalone either - but I guess the examples are still useful for Unity based game servers (which are hosted in the same private network as the database). One thing I'd recommend is putting the database communication to a separate thread (this can get very tricky, especially in Unity - so be careful and understand what you're doing). That way, stalled database calls can't freeze the game loop.

    Also, make sure to *always* close your connections. The most elegant way of doing this is using the "using" statement on any variables that need to be closed (see also: using Statement (C# Reference)). Not sure is something equivalent exists in UnityScript; if not, using try { ...*} finally { .... } also works quite well (and if that doesn't exist in UnityScript - do not use UnityScript ;-) ).
     
  17. alienx2

    alienx2

    Joined:
    Nov 19, 2010
    Posts:
    40
    I tested this with mySQL server on local my MAC.. then its error...

    here error log:
    Code (csharp):
    1.  
    2. SocketException: Connection refused
    3. System.Net.Sockets.Socket.Connect (System.Net.EndPoint remoteEP, Boolean requireSocketPolicy)
    4. System.Net.Sockets.Socket+Worker.Connect ()
    5. Rethrow as TdsInternalException: Server does not exist or connection refused.
    6. Mono.Data.Tds.Protocol.TdsComm..ctor (System.String dataSource, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion tdsVersion)
    7. Mono.Data.Tds.Protocol.Tds..ctor (System.String dataSource, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion tdsVersion)
    8. Mono.Data.Tds.Protocol.Tds70..ctor (System.String server, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion version)
    9. Mono.Data.Tds.Protocol.Tds80..ctor (System.String server, Int32 port, Int32 packetSize, Int32 timeout)
    10. Mono.Data.Tds.Protocol.TdsConnectionPoolManager.CreateConnection (Mono.Data.Tds.Protocol.TdsConnectionInfo info)
    11. Mono.Data.Tds.Protocol.TdsConnectionPool.GetConnection ()
    12. System.Data.SqlClient.SqlConnection.Open ()
     
  18. darkben

    darkben

    Joined:
    Jan 8, 2011
    Posts:
    405
    can anyone direct me to a MySQL tutorial to set it up and start building a database? I have no idea what I'm doing with the database. I have stuff installed but can't really get it to do anything...
     
  19. appels

    appels

    Joined:
    Jun 25, 2010
    Posts:
    2,686
  20. adamascj

    adamascj

    Joined:
    Apr 22, 2010
    Posts:
    55
  21. onllm

    onllm

    Joined:
    Mar 4, 2011
    Posts:
    111
    does it work with mysql?
     
  22. tonyf1121

    tonyf1121

    Joined:
    Apr 24, 2011
    Posts:
    42
    Im getting some errors from the cmsql var

    Assets/LogIn.js(23,37): UCE0001: ';' expected. Insert a semicolon at the end.
    Assets/LogIn.js(23,41): BCE0043: Unexpected token: ,.
    Assets/LogIn.js(23,42): UCE0001: ';' expected. Insert a semicolon at the end.
    Assets/LogIn.js(23,47): BCE0043: Unexpected token: ,.
    Assets/LogIn.js(23,48): UCE0001: ';' expected. Insert a semicolon at the end.
    Assets/LogIn.js(23,53): UCE0001: ';' expected. Insert a semicolon at the end.
    Assets/LogIn.js(23,58): UCE0001: ';' expected. Insert a semicolon at the end.


    Could somebody please help?
     
  23. MrDude

    MrDude

    Joined:
    Sep 21, 2006
    Posts:
    2,569
    I am curious about what was said about security. Here is the setup I have in mind for my games, please provide me with your valuable comments with regards to security issues, please:

    1. I setup my account info via MySQL on the website
    2. I create an authorative server that runs on uLink and accesses the data from my database and sends data to the clients
    3. For log-on purposes, the client's send WWW requests to PHP files on the website that then accesses the database and returns results directly to the client.
    4. After login details are confirmed, the client connects to the game server and passes it the validated username. From there, only the server accesses the database (directly or via PHP, not decided yet)

    The reasoning is that the login is done in PHP on the server so there really is no harm in having the clients send request directly there and save the game server that additional work. Once the client receives a message from the PHP files that their login details was successfully validated, the client then starts it's routines to log in to the game server. Thus, if the user has no account, they can create and validate an account without ever sending a single message to the game server. Once it is confirmed that they DO have an account, they can log in to the game server and the game server now starts spending resources on the new player.

    Now, what concerns me is the fact that there is data stored on the server in the first place. Like said before, websites can be hacked. If data is sent from the client to PHP or to a game server, it is still the same data being sent and the same data being returned wether it comes from the PHP file or from the game server. If the data is being stolen via (I think they call it a 'sniffer') that simply evaluates the data coming or going to/from a port, then how does it matter wether a database is accessed directly from a game or via PHP or via PHP via a game?

    Would you say the setup I mentioned above is secure or does the client accessing the database directly through the PHP on the websites causing a security risk? Should this go through the game server also? And if so, again, it is still the same username and password data that is being sent from the player's computer. How does the intended target of the data make one method more safe over the other seeing as the database itself is hidden from the clients via abstraction through the game server and/or the PHP? the only thing that really bothers me, though, is wether the fact that this data sniffer can determine the location of my PHP files, if that might cause a problem. If so, does that mean that accessing the php files from an account login page on my website also puts the data at risk because anyone who knows how to right click and say "View source" would also know where my php files are located... Yet, with php files that are marked as execute only, I thought that is the very reason WHY they are so safe... If simply knowing their location is a security risk then wtf? How is any database access secure?

    How/ why do people say that it is a security risk to access databases directly from Unity, and that doing it via PHP is better, but "websites can be hacked"... This whole security issue is confusing the living daylights out of me.
     
    Last edited: May 10, 2011
  24. Quietus2

    Quietus2

    Joined:
    Mar 28, 2008
    Posts:
    2,060
    There is no way to restrict record level access in a database. You can have either read/write/update authority to a table only. So with direct access to the tables you can conceivably not just retrieve everyone's credit card information, but change everyone's name to random letters if you so desired. After all, they need update authority to the table to create an account.

    With PHP in the middle, not only is the database structure hidden from you but you are restricted in terms of what access you have. You can't simply connect via ODBC and issue an SQL statement giving you every in-game purchased item and another to remove all the goodies from your in-game enemies.

    You can only do what the form and the PHP script allows you to do. There's data validation.
     
  25. appels

    appels

    Joined:
    Jun 25, 2010
    Posts:
    2,686
    The ideal situation would be to have the client talking directly to the server using an ecryption scheme, that way the data can't be read or sniffed. But that would mean more traffic. The problem with php is that it's clear text, can easily be read from the network.
    The reason why you should use php instead of having the database info in the client for direct connection is obvious, they hack the client software and they can delete your database or maybe even bring the server to it's knees.
    If they sniff a user password ( which you don't wan't either ) it's still less harmfull then having access to your database.
    User data can easily be modiefied if needed.
     
  26. MrDude

    MrDude

    Joined:
    Sep 21, 2006
    Posts:
    2,569
    @Quietus:
    So based on that answer, I would conclude that the setup I explained above is actually secure enough, yes?
    Client access database via PHP until account is validated. From there, only the game server access the database and passes data along...

    @appels
    So it seems I am not a complete idiot on the subject. It seems my thinking is correct, it is merely my fears / concerns that come to the forefront way too easily when I read about other people's concerns/ concerns :)

    The reasoning I followed is that the client will only send a username or password and the only result the php file will ever send the client is "Yes" or "No". So it doesn't matter wether anyone can directly access those scripts with fake data as a 'yes' or 'no' wouldn't be much useful to them. On the other hand, the new account creation feature could fill up my database real quickly, but at least it won't corrupt existing user's data. The main concern for me is the "update personal data" feature... That poses a bit of a risk, however, i overcome that by having to submit a username and password before you can get to that page in the game, and when you send the update info the username and password is sent again so again, hackers can't corrupt existing user's data.

    Once the game starts and I start sending damage values and positions and stuff like that, well, there the server has to be in charge of everything so it only makes sense to do everything through the server. I wanna keep a much traffic from the server as I can but I want to keep the dat as secure as I can at the same time...
     
    Last edited: May 10, 2011
  27. Quietus2

    Quietus2

    Joined:
    Mar 28, 2008
    Posts:
    2,060
    You're on the right track, yes. The only thing I would add is not to send data to your PHP script via clear text for anything critical such as account information.

    If you search google for info on php encryption, you will find a good number of options. Something low-risk security wise like a high score table, an MD5 hash is good enough. I certainly wouldn't use that for a credit card though, as the key is easily extractable from the Unity source code.
     
  28. MrDude

    MrDude

    Joined:
    Sep 21, 2006
    Posts:
    2,569
    Credit cards?

    I am way to new to databses to consider asking for and taking the responsibility of storing and keeping safe credit card info! :p
    No, I reckon storing a paypal address and conducting all my business via PayPal is way safer for me.

    I noticed on one of the other threads I read that a guy had a secret string in his script and when submitting data he would do something like:
    Code (csharp):
    1. var myString = username+","+password+","+mySecretString
    He would then hash5 (or MD5 or something or other) that string and compare it with a string on the PHP end. I forgot where I saw that but that was the next thing I wanted to look at. Looking at my reasoning above, I thought my security concerns were handled on the PHP end but the one thing I simply did not like at all was the sending of the username and password as plain text... although I reckon that is how it is done on the webpages so it can't be all bad???

    I actually planned on having the login php validate the accoutn and return only the UID that the server would use to access all other data for that player but the thought of sending THAT little bit of info from PHP to the client and having the client send that info to the server meant giving away the key bit of info, potentially twice, so I figured "hell no! Let the client validate the account an when validated, let the server retrieve the data again and obtain the UID itself!"
     
  29. appels

    appels

    Joined:
    Jun 25, 2010
    Posts:
    2,686
    it won't protect you 100% but it will make life harder on sniffers. the less critical data you send over the wire, the better. I also think that many hacking attemps are just people trying stuff out and they will abandon if they don't succeed what they are doing from the first time. Security is another big chapter like networking, you will have to evaluate based on your application whats critical and what isn't.
     
  30. MrDude

    MrDude

    Joined:
    Sep 21, 2006
    Posts:
    2,569
    Oh nooooo!!!!!!! Now why did you have to go and depress me like that!

    After all that evaluation and learning of various networking back ends onto to finally find out that uLink is by far the best option (I should work for them cause I just love promoting them :p ) now I have to do the same amount of work to figure out security? Sheesh! And I just got the hang of MySQL and then last night my bro sends me another link to NoSQL info and I find myself thinking that UnityPark is releasing a companion product to uLink that actually deals with NoSQL. I am wondering if they, great gods of coding that they are, are building security into their system so that I could just go:
    Code (csharp):
    1. networkObject.SendAnyAndAllDataSecurely(myDataObject)
    and it will take care of allll security issues for me. I could then just stick with one company and just use their stuff and they do all the heavy lifting for me...

    What do you think? Am I expectig maybe just a tiny little tad bit too much from them? :p
     
  31. appels

    appels

    Joined:
    Jun 25, 2010
    Posts:
    2,686
    in a client/server environment it's better to have the client only talk with the server and let the server handle all the stuff towards databases and more. That way you only have to secure the client connection to the server. The client will never have any info available on how your backend structure is setup or how to access your website.
    Would a company provide 100% security to your application ? No, there is no such thing as 100% secure.
     
  32. brust

    brust

    Joined:
    Jul 13, 2010
    Posts:
    10
    I agree with Appels, even the Plastation Network have failed, but secure is important, but, not all users know how to hack a database, i'm one that don't know how to do it.
     
  33. prefix

    prefix

    Joined:
    Sep 26, 2011
    Posts:
    73
    There has been many points and suggestions made here. But for an MMO, which of the following examples would be the "best" route?
    Thanks all, i'm a visual person :)

     
    Last edited: Oct 17, 2011
  34. drafa

    drafa

    Joined:
    Nov 10, 2011
    Posts:
    7
    hi community. I have a problem with mysql connection, my english is very bad, (i cant speak very good...)
    I create this project: www.mediafire.com/?j5gg26r88g4lz7v
    and have a problem, have a unity exception:

    SocketException: Host desconocido.

    System.Net.Dns.GetHostByName (System.String hostName)
    System.Net.Dns.GetHostEntry (System.String hostNameOrAddress)
    Mono.Data.Tds.Protocol.TdsComm..ctor (System.String dataSource, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion tdsVersion)
    Rethrow as TdsInternalException: Server does not exist or connection refused.
    Mono.Data.Tds.Protocol.TdsComm..ctor (System.String dataSource, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion tdsVersion)
    Mono.Data.Tds.Protocol.Tds..ctor (System.String dataSource, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion tdsVersion)
    Mono.Data.Tds.Protocol.Tds70..ctor (System.String server, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion version)
    Mono.Data.Tds.Protocol.Tds80..ctor (System.String server, Int32 port, Int32 packetSize, Int32 timeout)
    Mono.Data.Tds.Protocol.TdsConnectionPoolManager.CreateConnection (Mono.Data.Tds.Protocol.TdsConnectionInfo info)
    Mono.Data.Tds.Protocol.TdsConnectionPool.GetConnection ()
    System.Data.SqlClient.SqlConnection.Open ()
    Rethrow as SqlException: Server does not exist or connection refused.
    System.Data.SqlClient.SqlConnection.Open ()
    DBConnect.Start () (at C:/Datos/becatecinf/Unity/DBConnection/Assets/Scripts/DBConnect.js:19)


    anyone can help me please? thanks for read me
     
  35. drafa

    drafa

    Joined:
    Nov 10, 2011
    Posts:
    7
  36. xtplpune

    xtplpune

    Joined:
    Feb 24, 2012
    Posts:
    2
    Your feedback gives me the proper fuel to continue my work......


    nice tag yar!!!
     
  37. otto123

    otto123

    Joined:
    Jul 19, 2012
    Posts:
    5
    Obrigado pelo tutorial. Finalmente achei alguém do Brasil nesse forum kkkkkkkkkk
    ainda melhor, achei alguém que sabia justamente o que eu queria =)

    Thankz
     
  38. Reahreic

    Reahreic

    Joined:
    Mar 23, 2011
    Posts:
    155
    I wouldn't use the MD5 hashing algorithm as it's already been beaten (i use the word beaten to avoid going into heavy details about collisions, and rainbow tables). You should rather use one of the SHA algos i personally use RIPEMD320. PHP 5.3.0 has support for several great hash algos detailed here http://www.php.net/manual/en/function.hash-algos.php

    Cryptographic hashing is a great method to secure data and unlike encryption there is no easy way to decrypt the the hashed result. To add security you can generate a random 256bit hexadecimal key that is then hashed and added to the hashed password, then hash the combined string for even more protection. Research salting hashed passwords.

    After that look into using PDO statements instead of regular sql queries to query your database, they're much more secure vs sql injection attacks and faster that regular queries of i recall.

    It may seem overwhelming to begin with but after about a week of reading and research i'm sure you'll be able to understand it all.

    If i remember, after work i'll grab my php login portal script that i use and post some of its contents here as an example. It could stand to be improved still but so far it seems to hold up. **Knocks on wood**
     
  39. Reahreic

    Reahreic

    Joined:
    Mar 23, 2011
    Posts:
    155
    As promised here's an example php script similar to the one i use to authenticate the login for a collaboration portal. Feedback on script quality is always welcomed. And to correct my earlier post i used RIPEMD256 as the hashing algo.

    The active username and session are stored only as long as the browser is open, no remember my login cookies are used. Each secured page uses something like the below to verify an active session to allow or kick the user. As always you can store any info in the session, but remember that a session can be spoofed so i store minimal info in it.

    Code (csharp):
    1. session_start();
    2.     if (!(isset($_SESSION['LoggedIn'])  $_SESSION['LoggedIn'] != '')) {
    3.             header ("Location: Login.php");
    4.             exit();
    5.         }
    The below is the authentication code, it used PDO queries here's the info on PDO vs sql queries. http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers
    It helps that php_helper_account only has SELECT permissions, when i need to update i use a different account that is used sparingly, and no account has delete. instead of delete i simply throw a inactive flag that gets cleaned up later from a local only accessible page and function.

    Each salt is unique to the user, there's another more secure page that handles creating them when a password is changed or created. Here i used 1 salt but you can use as many as you feel like coding for. you can even split the string into single char substrings and add a unique salt between each character, then hash it all together to really make a brute force attack difficult. (one must draw the line somewhere and im not that crazy, but for Credit Card numbers i might just do it. Albeit it not in the manner i've described anywhere publically)
    Code (csharp):
    1.  
    2. //This file processes the login information, then if fails login returns to Login.php with error message set in session
    3. //remember to clear session error if successfull login is made.
    4.     $num_rows;
    5.     $uname;
    6.     $pword;
    7.     $salt;
    8.     //=======================================================================
    9.     //  CONNECT TO THE SERVER DATABASE NEVER LET THE CLIENT SEE THESE DETAILS
    10.     //=======================================================================
    11.     $user_name = "php_helper_account";
    12.     $pass_word = "You'd love to know this";
    13.     $database = "myDatabaseName_1";
    14.     $server = "me.myDBserver.com";
    15.     $db = new PDO("mysql:host=".$server.";dbname=".$database.";", $user_name, $pass_word);
    16.    
    17.     //=============================================
    18.     // Prepare sql statements that are used defeat sql injection
    19.     //=============================================
    20.     $saltStmt = $db->prepare("SELECT salt FROM Users WHERE userName = ?");
    21.     $loginStmt = $db->prepare("SELECT * FROM Users WHERE userName = ? AND pass = ?");
    22.     $firstTimeStmt = $db->prepare("SELECT * FROM Users WHERE userName = ? AND firstLogin = ?");
    23.    
    24.     if ($_SERVER['REQUEST_METHOD'] == 'POST'){
    25.         $uname = $_POST['txtUserName'];
    26.         $pword = $_POST['txtPassword'];
    27.         //salt statement execytuin
    28.         $saltStmt->execute(array($uname));
    29.         //Returns the data from the database that matchs the query
    30.         $result = $saltStmt->fetch(PDO::FETCH_ASSOC);
    31.         //Use column name for index in returned array
    32.         $salt = $result["salt"];
    33.         //Hash salted pass
    34.         $pword = hash('ripemd256',$salt . $pword);
    35.            
    36.         //login statement execution
    37.         $loginStmt->execute(array($uname, $pword));
    38.         $result = $loginStmt->fetch(PDO::FETCH_ASSOC);
    39.  
    40.         if ($result) {
    41.             session_start();
    42.             $_SESSION['LoggedIn'] = "1";
    43.             $_SESSION['UserName'] = $uname;
    44.            
    45.             $firstTimeStmt->execute(array($uname, 1));
    46.             //Returns the rows from thge database that match the query
    47.             $rows = $firstTimeStmt->fetchAll(PDO::FETCH_ASSOC);
    48.             //Shows the number of rows returned
    49.             $num_rows = $firstTimeStmt->rowCount();
    50.             if($num_rows >= 1){
    51.                 //Change password section
    52.                 //Remove session error var
    53.                 if (isset($_SESSION['Error'])) {
    54.                     unset($_SESSION['Error']);
    55.                 }
    56.                 //Redirect
    57.                 header ("Location: ChangePass.php");
    58.                 exit();
    59.             }else{
    60.                 //Logging in, Please wait... section
    61.                 //Remove session error var
    62.                 if (isset($_SESSION['Error'])) {
    63.                     unset($_SESSION['Error']);
    64.                 }
    65.                 //Redirect
    66.                 header ("Location: FileBrowser.php");
    67.                 exit();
    68.             }
    69.         }else {
    70.             session_start();
    71.             $_SESSION['LoggedIn'] = "";
    72.             $_SESSION['Error'] = "Incorrect username or password.";
    73.             header ("Location: Login.php");
    74.             exit();
    75.         }
    76.     }
    Oh and never reveal your security code or leave comments in it. My specific implementation of this method as shown in the code above is now obsolete as its been posted publicly on the internet. Use the methods above and create your own implementation, and be creative spaghetti and weird vars all over the place intertwining in all kinds of horrific ways makes it a nightmare to read, debug or hcxk/decipher.
     
  40. Meltdown

    Meltdown

    Joined:
    Oct 13, 2010
    Posts:
    5,527
    I'd recommend setting up web services or WCF services on your web server, and let your game client talk to those.
    Your web service then talks to the database. You should NEVER provide database connection credentials on client software. That's a big NO NO.
    It will be fairly easy for hackers to find it. Once they have that your db is compromised and all your customers lose faith.
     
  41. quantum_rez

    quantum_rez

    Joined:
    Oct 23, 2012
    Posts:
    35
    ok i'm trying to use this code, and error, i don't understand what the error meaning.

    This is my error :
    1. NullReferenceException: Object reference not set to an instance of an object
    Database..ctor () (at Assets/Database.js:27)

    2. SocketException: No connection could be made because the target machine actively refused it.

    System.Net.Sockets.Socket.Connect (System.Net.EndPoint remoteEP, Boolean requireSocketPolicy)
    System.Net.Sockets.Socket+Worker.Connect ()
    Rethrow as TdsInternalException: Server does not exist or connection refused.
    Mono.Data.Tds.Protocol.TdsComm..ctor (System.String dataSource, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion tdsVersion)
    Mono.Data.Tds.Protocol.Tds..ctor (System.String dataSource, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion tdsVersion)
    Mono.Data.Tds.Protocol.Tds70..ctor (System.String server, Int32 port, Int32 packetSize, Int32 timeout, TdsVersion version)
    Mono.Data.Tds.Protocol.Tds80..ctor (System.String server, Int32 port, Int32 packetSize, Int32 timeout)
    Mono.Data.Tds.Protocol.TdsConnectionPoolManager.CreateConnection (Mono.Data.Tds.Protocol.TdsConnectionInfo info)
    Mono.Data.Tds.Protocol.TdsConnectionPool.GetConnection ()
    System.Data.SqlClient.SqlConnection.Open ()
    Rethrow as SqlException: Server does not exist or connection refused.
    System.Data.SqlClient.SqlConnection.Open ()
    Database.Main () (at Assets/Database.js:23)

    3. NullReferenceException: Object reference not set to an instance of an object
    Database.Start () (at Assets/Database.js:44)

    can somebody help me here ? Thanks for your attention :D

    this is my code after i change it :
    Code (csharp):
    1. #pragma strict
    2.  
    3. import System;
    4.  
    5. import System.Data;
    6.  
    7. import System.Data.SqlClient;
    8.  
    9. var dbcon : IDbConnection;
    10.  
    11.     var connectionString : String =
    12.    
    13.         "Server=IP;" + // put the ip here!
    14.    
    15.         "Database=MyDatabaseName;" +
    16.    
    17.         "User ID=MyUserID;" +
    18.    
    19.         "Password=MyPassword;";
    20.    
    21.     dbcon = new SqlConnection(connectionString);
    22.    
    23.     dbcon.Open();
    24.    
    25.     //create the class EXECUTIONER, that execute SQL commands
    26.    
    27.     var dbcmd : IDbCommand = dbcon.CreateCommand();
    28.    
    29.     //string var, to save the command we want to use
    30.    
    31.     var cmdSql : String = "SELECT Name FROM demo_table";
    32.    
    33.     //we add the command, as string, to the executor, to shot it!
    34.    
    35.     dbcmd.CommandText = cmdSql;
    36.    
    37.     //and then, we create a table, like a normal db table, to use it on unity, and we use the function that "plays" the command
    38.    
    39.     var reader : IDataReader = dbcmd.ExecuteReader();
    40.  
    41.  
    42. function Start () {
    43.  
    44.     while(reader .Read()) {
    45.  
    46.         var name : String = reader ["Name"].ToString();
    47.  
    48.         print ("NAME: " + name);
    49.  
    50.     }
    51.    
    52.     reader .Close();
    53.  
    54.     reader = null;
    55.    
    56.     dbcon.Close();
    57.    
    58.     dbcon = null;
    59.  
    60. }
    61.  
    62. function Update () {
    63.  
    64. }
     
    Last edited: Nov 20, 2012
  42. Antonief

    Antonief

    Joined:
    Nov 28, 2012
    Posts:
    2
    hello to everyone.
    I work a job and I want when the user of the game, play with an object A1, A1 object to connect to the database.
    How to display the data of A1 object from the database within UNITY;

    Thanks in advance

    Sorry for my english
     
  43. GradyLorenzo

    GradyLorenzo

    Joined:
    Jan 17, 2010
    Posts:
    155
    Having the same issue. Any solutions?
     
  44. AnschauAnderson

    AnschauAnderson

    Joined:
    Jul 22, 2013
    Posts:
    1
    I did mine this way


    Code (csharp):
    1. #pragma strict
    2. import System;
    3. import System.Data;
    4. import System.Data.SqlClient;
    5.  
    6. var dbcon : IDbConnection;
    7. var connectionString : String;
    8.  
    9. function Start () {
    10.  
    11. connectionString = "Server=IP; Database=MyDataBase;User ID=MyUser; Password=MyPassWord";
    12.  
    13. }
    14.  
    15. function Update ()
    16. {
    17.  
    18.    
    19.  
    20. }
    21. function OnGUI()
    22. {
    23.    
    24.     if (GUI.Button(Rect(200,200,100,100),"Login"))
    25.     {
    26.         dbcon = new SqlConnection(connectionString);
    27.         dbcon.Open();
    28.                 //create the class EXECUTIONER, that execute SQL commands      
    29.         var dbcmd : IDbCommand = dbcon.CreateCommand();
    30.        
    31.         //string var, to save the command we want to use       
    32.         var cmdSql : String = "SELECT NOME FROM CURSO";
    33.        
    34.         //we add the command, as string, to the executor, to shot it!      
    35.         dbcmd.CommandText = cmdSql;
    36.        
    37.         //and then, we create a table, like a normal db table, to use it on unity, and we use the function that "plays" the command    
    38.         var reader : IDataReader = dbcmd.ExecuteReader();
    39.        
    40.         while(reader .Read()) {
    41.             var nome : String = reader ["NOME"].ToString();
    42.             print ("NAME: " + nome);
    43.         }
    44.         reader .Close();
    45.         reader = null;
    46.         dbcon.Close();
    47.         dbcon = null;
    48.     }
    49.  
    50. }
     
  45. frankmat

    frankmat

    Joined:
    Sep 14, 2013
    Posts:
    42
    Whoa... storing SQL Server passwords in your code.... I know people have said this before... but this is fraught with danger. You are basically giving them access to get into your database and do whatever they want... including deleting data.
     
  46. Diodel

    Diodel

    Joined:
    Aug 20, 2014
    Posts:
    2
    hi? im a new here, im making a 3d simulation of our school campus, that allows user to explore inside the campus,simulate what are the process of enrollment and view basic information of personel in every rooms and offices.
    i want to have 2 user type the admin(allowed to make changes on the game likes adding,edting and deleting information given by the game)
    user(allowed to view only information and not allowed to do changes)
    and i want to have a game that if every time i will make changes in admin side its automatically change in user side evrything changes ive done in admin side.

    is it possible?
    updating the database through the stand alone game.

    if someone can help me about this i will give full credit :D
    and if possible be implented to our school then money as well .
     
  47. Fenris2

    Fenris2

    Joined:
    Aug 25, 2014
    Posts:
    61
    application
    Generally speaking you are talking about role based security. Google will help with tutorials / discussions. I would recommend something like the following. . .

    Start with a minimum of two actual database user account types
    1. admin DB account. Kept only for you/your teams or whomever does actual database coding or admin work.
    2. An application account that is denied access to everything BUT the stored procedures you will use in your code. Google stored procedures for more on them.
    In this way the application is only allowed to perform the actions you explicitly design in your stored procedures, and SPs can be optimized far easier by the DB then dynamic queries. Security/performance hint: do not simply then have the stored procedures allow dynamic SQL that defeats the purpose.

    Now make a table of users with a user id, encrypted password and an access level (Admin or user) fields. Note this means for your application NOT the DB admin as described above.

    The user logs in and authenticates gaining the application access level of either admin or user.

    The stored procedures that read data will merely check the user exists and is authenticated before running whatever SQL they do

    The stored procedures that write/delete or insert data will check that the user is also an admin before running whatever SQL they do

    I am brand new to Unity , but here is a google provided example as far as unity C# code goes: http://answers.unity3d.com/questions/8171/connect-to-the-sql-database.html

    The connection string piece will need to specify the db server and the application account info. Note that if you do this in your 'game' code it could be decompliled. So, if that is a risk you would NOT want you actually have another layer of server code that your client calls that actually talks to the db. However, In our case, the worst that could happen would be they could call the read data stored procedures, the write/update/delete ones would still bounce them as they would not be an 'admin' useer. Still bad, but for a start it will do.
     
    Last edited: Sep 11, 2014
  48. GradyLorenzo

    GradyLorenzo

    Joined:
    Jan 17, 2010
    Posts:
    155
    I ultimately just ended up parsing a PHP that did the queries for me. Seems more secure that way, since your DB details aren't stored client side.
     
  49. sandolkakos

    sandolkakos

    Joined:
    Jun 3, 2009
    Posts:
    190
    Same here :) And also, what you can to do, is encrypt the strings you send/receive (using a custom function) in order to give you more security :)
     
  50. donamin1

    donamin1

    Joined:
    Apr 4, 2013
    Posts:
    26
    Hi.
    Thanks for your great tutorial.
    I have one question: I'm working on an online game where i need to store information of each player as an individual file on an online host.
    How is that possible?
     
unityunity