Recent

Author Topic: [HELP] Connecting to a SQL Database tables?- Console Application  (Read 33184 times)

rvk

  • Hero Member
  • *****
  • Posts: 4324
Re: [HELP] Connecting to a SQL Database tables?- Console Application
« Reply #15 on: December 18, 2014, 10:26:36 pm »
Let me check the complete code.... (I haven't got one here where password is global). I'll post the complete code in a moment.

rvk

  • Hero Member
  • *****
  • Posts: 4324
Re: [HELP] Connecting to a SQL Database tables?- Console Application
« Reply #16 on: December 18, 2014, 10:32:17 pm »
This should work:

In this version password doesn't need to be global and I check during input there is always a non-empty username and password.

You could of course redesign the complete user/password input to be more flexible (i.e. make a possibility to escape the program without entering a username or password) but this works for now.
 
Code: [Select]
program game1;

uses
  sqldb,
  mysql56conn,
  SysUtils;

var
  AConnection: TSQLConnector;
  User: string;

  procedure CreateConnection;
  begin
    AConnection := TSQLConnector.Create(nil);
    AConnection.ConnectorType := 'MySQL 5.6';
    AConnection.Hostname := 'sql5.freemysqlhosting.net';
    AConnection.DatabaseName := 'sql561707';
    AConnection.UserName := 'sql561707';
    AConnection.Password := 'dX3%eP3!';
  end;

  function GetValidUsername: boolean;
  var
    Query: TSQLQuery;
    Password: string;
  begin
    Result := False;
    repeat
      Write('Username: ');
      ReadLn(User);
    until User <> '';
    repeat
      Write('Password: ');
      ReadLn(Password);
    until Password <> '';
    Query := TSQLQuery.Create(nil);
    Query.Database := AConnection;
    Query.Transaction := TSQLTransaction.Create(Query);
    Query.Transaction.Database := AConnection;
    Query.SQL.Text := 'select * from users where username=:username and password=:password';
    Query.ParamByName('username').AsString := User;
    Query.ParamByName('password').AsString := Password;
    Query.Open;
    Result := Query.RecordCount > 0;
    Query.Close;
    Query.Free;
  end;

begin
  SetCurrentDir(GetCurrentDir + '\lib');
  CreateConnection;
  AConnection.Open;
  if Aconnection.Connected then
  begin
    writeln('Successful connect!');
    if GetValidUsername then
      GotoTheGame(User) // YOUR GAME PROCEDURE
    else
      writeln('Wrong username or password!');
  end
  else
    writeln('This is not possible, because if the connection failed, an exception should be raised, so this code would not be executed');
  AConnection.Close;
  AConnection.Free;
  writeln('End program!');
  readln;
end.

nobodyknowsme

  • Full Member
  • ***
  • Posts: 114
Re: [HELP] Connecting to a SQL Database tables?- Console Application
« Reply #17 on: December 18, 2014, 10:55:06 pm »
Dont need that now :) I figured it out, it was me. Thank you so much. You've been a great help!

nobodyknowsme

  • Full Member
  • ***
  • Posts: 114
Re: [HELP] Connecting to a SQL Database tables?- Console Application
« Reply #18 on: December 18, 2014, 10:56:27 pm »
1) How would I move the dll file? I don't want it to be in the root of the folder as it looks ugly.
2) How secure is this?
Yes... you can put libmysql.dll in a subdirectory (for example lib) and put this at the top of your program:
Code: [Select]
  SetCurrentDir(GetCurrentDir + '\lib');
Your current path after this is lib (in your program directory) and Lazarus will load libmysql.dll from there. If you need the current path to be that of your .exe afterwards you can set it back right after the CreateConnection-call.
(PS. You do need to add SysUtils to your uses-clause.)

The security depends on what you call "security" and on your users. (I already mentioned this in an earlier post) If there are really Tech-savvy  they won't have trouble finding out the username and password of your database. Others (with no access to the game) can only try to brute-force hack your account but seeing as there is no other option I think that wouldn't be a problem (as the default password is already strong).

When you say Tech-savvy what do you mean? Are there any ways of preventing them from hacking us?

rvk

  • Hero Member
  • *****
  • Posts: 4324
Re: [HELP] Connecting to a SQL Database tables?- Console Application
« Reply #19 on: December 18, 2014, 11:15:12 pm »
When you say Tech-savvy what do you mean? Are there any ways of preventing them from hacking us?
With Tech-savvy I mean people with a bit of computer-skills to install and run a program to capture network traffic on their computer. They could see the communication from your program to the MySQL-server and figure out the username and password of that server. After they have that they can do anything with your database they like.

So it depends on what kind of users you have and if you think they might do that.

Securing it is very difficult (at least with a plain MySQL-connection). You could try MySQL with a SSL connection (which your current MySQL-provider does not support) but you could also go an entirely different route... and that's the PHP-solution I mentioned earlier.

What kind of data do you keep in that database???
If it's just username and password and the game only needs to check it, you are far better off with a closed MySQL-database (so nobody from the outside can connect to it except your own website and PHP-pages). Almost every web-provider provides such MySQL databases.

Your program could call http://www.mydomain.com/user_check.php?username=mememe&password=secret (this is an example)
and your php-script could echo Correct or Incorrect back from which your program would abort or continue.

Even adding scores could be done like this:
http://www.mydomain.com/add_score.php?username=mememe&score=20&security_check=ahfjhasjfhsakjhfk

The security_check here is something you are sure only your program can calculate and is dependend on the score and time so a gamer can't just put this url in a browser and have his score updated. That's where the tricky part lies that a gamer could find this algorithm and give himself a large score. But that's far more difficult then capturing traffic and gaining access to your database for the gamer.

That's the most secure solution I can think of (because your MySQL is completely closed off). But again... it depends on what kind of users you have. Maybe your users are not that technical or it doesn't really matter if somebody gains access to the database. But if it does matter you should think about that now.

nobodyknowsme

  • Full Member
  • ***
  • Posts: 114
Re: [HELP] Connecting to a SQL Database tables?- Console Application
« Reply #20 on: December 18, 2014, 11:19:42 pm »
I think having a closed SQL-database would be better. And yes, it just needs to check there usernames and passwords. Thats it. How much securer will that be having a closed SQL database? How easy will this be to set up? and for the program we've just written will it need to change much for the closed SQL database?
« Last Edit: December 18, 2014, 11:23:35 pm by nobodyknowsme »

rvk

  • Hero Member
  • *****
  • Posts: 4324
Re: [HELP] Connecting to a SQL Database tables?- Console Application
« Reply #21 on: December 18, 2014, 11:25:21 pm »
I think having a closed SQL-database would be better. And yes, it just needs to check there usernames and passwords. Thats it. How much securer will that be having a closed SQL database?
If for now the game only needs to check username and password the PHP-solution would be much much and much more secure than the "open" MySQL solution.

nobodyknowsme

  • Full Member
  • ***
  • Posts: 114
Re: [HELP] Connecting to a SQL Database tables?- Console Application
« Reply #22 on: December 18, 2014, 11:26:12 pm »
Okay thats cool. How hard would it be for the closed SQL?

nobodyknowsme

  • Full Member
  • ***
  • Posts: 114
Re: [HELP] Connecting to a SQL Database tables?- Console Application
« Reply #23 on: December 18, 2014, 11:28:07 pm »
When you say Tech-savvy what do you mean? Are there any ways of preventing them from hacking us?
With Tech-savvy I mean people with a bit of computer-skills to install and run a program to capture network traffic on their computer. They could see the communication from your program to the MySQL-server and figure out the username and password of that server. After they have that they can do anything with your database they like.

So it depends on what kind of users you have and if you think they might do that.

Securing it is very difficult (at least with a plain MySQL-connection). You could try MySQL with a SSL connection (which your current MySQL-provider does not support) but you could also go an entirely different route... and that's the PHP-solution I mentioned earlier.

What kind of data do you keep in that database???
If it's just username and password and the game only needs to check it, you are far better off with a closed MySQL-database (so nobody from the outside can connect to it except your own website and PHP-pages). Almost every web-provider provides such MySQL databases.

Your program could call http://www.mydomain.com/user_check.php?username=mememe&password=secret (this is an example)
and your php-script could echo Correct or Incorrect back from which your program would abort or continue.

Even adding scores could be done like this:
http://www.mydomain.com/add_score.php?username=mememe&score=20&security_check=ahfjhasjfhsakjhfk

The security_check here is something you are sure only your program can calculate and is dependend on the score and time so a gamer can't just put this url in a browser and have his score updated. That's where the tricky part lies that a gamer could find this algorithm and give himself a large score. But that's far more difficult then capturing traffic and gaining access to your database for the gamer.

That's the most secure solution I can think of (because your MySQL is completely closed off). But again... it depends on what kind of users you have. Maybe your users are not that technical or it doesn't really matter if somebody gains access to the database. But if it does matter you should think about that now.

Well I don't want people accessing our database at all. I want it as secure as possible. Would the closed SQL be hard and would the program we've just written now be useless? thank you again man

rvk

  • Hero Member
  • *****
  • Posts: 4324
Re: [HELP] Connecting to a SQL Database tables?- Console Application
« Reply #24 on: December 18, 2014, 11:50:37 pm »
Well I don't want people accessing our database at all. I want it as secure as possible. Would the closed SQL be hard and would the program we've just written now be useless? thank you again man
Yes... the code we just wrote in Lazarus can't be used in PHP. (there are some implementations of pascal for webpages I think but going with full-PHP would be more practicle)

It's not entirely in the scope of this forum (because it is mainly for Freepascal/Lazarus) but I hope a may make an exception so here is some PHP-code I come up with.

You could save this on any Website (with PHP and MySQL installed) and call it like this (resed.nl is a temp-site I used to upload this example):

http://www.resed.nl/check_user.php?username=mememe&password=thiswontwork
(will give you "Incorrect")

http://www.resed.nl/check_user.php?username=test&password=0
(will give you "Correct")

I used your database at freemysqlhosting but of course with your own implementation you should use a closed MySQL-database at a provider where you can set this webpage.

Code: [Select]
<html>
<body>

<?php
$old
=error_reporting(E_ALL);
ini_set('display_errors''1');

$sql="select * from users where username=:username and password=:password";
$databasename="sql561707";
$databasehost="sql5.freemysqlhosting.net";
$databaseuser="sql561707";
$databasepass="dX3%eP3!";

try {
  
$pdo = new PDO("mysql:host=$databasehost;dbname=$databasename"$databaseuser$databasepass);
  
$pdo->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
  
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARESfalse);
  
$query $pdo->prepare($sql);
  
$query->bindParam("username"$_GET["username"]);
  
$query->bindParam("password"$_GET["password"]);
  
$query->execute();
}
catch(
exception $e) {
  echo 
'Exception -> ';
  
var_dump($e->getMessage());
  exit();
}

if (
$query->rowCount() > 0) {
  print(
"Correct");
} else {
  print(
"Incorrect");
}

?>

</body>
</html>

It would be even more secure if you set the lines:
Code: [Select]
$databasename="sql561707";
$databasehost="sql5.freemysqlhosting.net";
$databaseuser="sql561707";
$databasepass="dX3%eP3!";
in a separate file "database.inc" in the private directory on your website and replace it in the code with:
Code: [Select]
include('/private/database.inc');
That way, when there is a problem with the PHP and if for some reason the PHP is shown (which it never should under normal circumstances) the database-details are not in the PHP but in an include-file which is never shown.

That's as secure as you can get.
Now all you need to do is implement the calling of this website in your project and read the result and check if it is Correct of Incorrect.
« Last Edit: December 18, 2014, 11:52:10 pm by rvk »

nobodyknowsme

  • Full Member
  • ***
  • Posts: 114
Re: [HELP] Connecting to a SQL Database tables?- Console Application
« Reply #25 on: December 19, 2014, 09:44:26 am »
Thank you so much. In the program for the databases if it doesn't connect it just ends up breaking.  If the "if Aconnection.Connected " doesn't work of the database doesn't connected how would I make it so instead if it breaking to just output "Server offline" on screen? As at college it doesn't connect and we cant show any the rest of our code

EDIT:

I tried "if not Aconnection.Connected then" but it is breaking at AConnection.Open; thats before any of that code so it is this breaking the code if it doesn't connect
« Last Edit: December 19, 2014, 09:46:55 am by nobodyknowsme »

rvk

  • Hero Member
  • *****
  • Posts: 4324
Re: [HELP] Connecting to a SQL Database tables?- Console Application
« Reply #26 on: December 19, 2014, 09:50:21 am »
Thank you so much. In the program for the databases if it doesn't connect it just ends up breaking.  If the "if Aconnection.Connected " doesn't work of the database doesn't connected how would I make it so instead if it breaking to just output "Server offline" on screen? As at college it doesn't connect and we cant show any the rest of our code
You should put the whole connection and check part between try and except.

So this would be the main part:
Code: [Select]
begin
  SetCurrentDir(GetCurrentDir + '\lib');
  try
    CreateConnection;
    AConnection.Open;
    if Aconnection.Connected then
    begin
      writeln('Successful connect!');
      if GetValidUsername then
        GotoTheGame(User) // YOUR GAME PROCEDURE
      else
        writeln('Wrong username or password!');
    end
    else
      writeln('This is not possible, because if the connection failed, an exception should be raised, so this code would not be executed');
    AConnection.Close;
  except
    on E: Exception do
      writeln('Error: ' + E.Message);
  end;
  AConnection.Free;
  writeln('End program!');
  readln;
end.
(Running this in the Lazarus IDE you will get a break/exception message but when clicking continue your program just continues. Outside the IDE your program just ends up in the exception-part and prints a message.)

Don't you have these kinds of constructions in the game itself? (As this is basic Object Pascal programming.)

nobodyknowsme

  • Full Member
  • ***
  • Posts: 114
Re: [HELP] Connecting to a SQL Database tables?- Console Application
« Reply #27 on: December 19, 2014, 10:18:58 am »
It still breaks at AConnection.Open;?
Thanks for the reply

nobodyknowsme

  • Full Member
  • ***
  • Posts: 114
Re: [HELP] Connecting to a SQL Database tables?- Console Application
« Reply #28 on: December 19, 2014, 10:20:04 am »
It still breaks at AConnection.Open;?
Thanks for the reply

No, our game is all offline :)

rvk

  • Hero Member
  • *****
  • Posts: 4324
Re: [HELP] Connecting to a SQL Database tables?- Console Application
« Reply #29 on: December 19, 2014, 10:28:43 am »
It still breaks at AConnection.Open;?
Are you running in the IDE or outside the IDE?

Outside the IDE you should get a message like:
Code: [Select]
Error: TMySQL56Connection : Server connect failed.
End program!

Inside the IDE you always get the exception dialog first (on which you can click continue).

What (exact) message are you getting ??? (just "it still breaks" is not a helpful message)

No, our game is all offline :)
Even if your game is offline... a construction like try/finally and try/except is standard programming practice in Object-Pascal. It prevents your program from crashing when something unexpected happens. I'm sure there should be some in your game too. If there is not, the game will end at some point, where you could have recovered it.

Some learning material (although there are lots more on the internet to be found):
http://www.schoolfreeware.com/Free_Pascal_Lazarus_App_GUI_Tutorial_8.html
http://www.delphibasics.co.uk/RTL.asp?Name=Try
http://stackoverflow.com/questions/6601147/how-to-correctly-write-try-finally-except-statements
« Last Edit: December 19, 2014, 10:34:03 am by rvk »

 

TinyPortal © 2005-2018