Results 1 to 16 of 16

Thread: How could I connect to MYSQL server if that server do not allowed any remote access?

  1. #1
    Join Date
    Jan 2011
    Posts
    127
    Thanks
    42
    Thanked 4 Times in 4 Posts
    Qt products
    Qt4
    Platforms
    Windows

    Default How could I connect to MYSQL server if that server do not allowed any remote access?

    For security reasons, our administrator do not allow any remote access but local access, anyway to access the DB by Qt in this kind of situation?If this is impossible, we have to use php + mysql to develop the system since it could allow the users to access the localhost by website.

  2. #2
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: How could I connect to MYSQL server if that server do not allowed any remote acce

    Of course it is not possible, that's what security mechanisms are for. Depending on your organisation subverting the security policy of the company is a firing offence.

  3. #3
    Join Date
    Jan 2011
    Posts
    127
    Thanks
    42
    Thanked 4 Times in 4 Posts
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: How could I connect to MYSQL server if that server do not allowed any remote acce

    Quote Originally Posted by ChrisW67 View Post
    Of course it is not possible, that's what security mechanisms are for. Depending on your organisation subverting the security policy of the company is a firing offence.
    Since it is impossible, we have no choice but prefer php to develop.Do not familiar with php, are there any model and view library like Qt(QTableView, QSqlTableModel) in php worth to recommend?Any development tool like QDesigner could ease the pains of deploying UI?
    Last edited by stereoMatching; 20th January 2014 at 06:57.

  4. #4
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: How could I connect to MYSQL server if that server do not allowed any remote acce

    If your project is actually important to the company then having the necessary security issues addressed should be part of your project plan.

    If you are forced to go down the web-based "application" route then there a many options for PHP (other than hand coding it all yourself):
    PHP development frameworks
    Nothing is nearly as drag-and-drop as Designer. There are also Microsoft .Net, Java, Python, Ruby and other options.

  5. The following user says thank you to ChrisW67 for this useful post:

    stereoMatching (29th January 2014)

  6. #5
    Join Date
    Jul 2011
    Location
    Italy
    Posts
    24
    Thanks
    2
    Thanked 4 Times in 4 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60 Maemo/MeeGo

    Default Re: How could I connect to MYSQL server if that server do not allowed any remote acce

    If you prefer to use Qt and a desktop application instead of a web one you can also code in php only the data management and use the qt application as client.
    Use php to access the data and return them in xml or (better) json, display them using qt.
    If security is important (probably it is) you'll also need to manage user security in your phps script.
    You'll also need to pay attention to record locks for concurrent updates.

  7. The following user says thank you to sakya for this useful post:

    stereoMatching (29th January 2014)

  8. #6
    Join Date
    Jan 2006
    Location
    Graz, Austria
    Posts
    8,416
    Thanks
    37
    Thanked 1,544 Times in 1,494 Posts
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: How could I connect to MYSQL server if that server do not allowed any remote acce

    Quote Originally Posted by stereoMatching View Post
    Since it is impossible, we have no choice but prefer php to develop.
    No, this is one option.

    Additional to the web service approach already mentioned there is also the option of splitting the Qt application in frontend (client) and backend (server).
    Or connecting through a VPN, an SSH tunnel, etc,

    Cheers,
    _

  9. The following user says thank you to anda_skoa for this useful post:

    stereoMatching (29th January 2014)

  10. #7
    Join Date
    Jan 2011
    Posts
    127
    Thanks
    42
    Thanked 4 Times in 4 Posts
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: How could I connect to MYSQL server if that server do not allowed any remote acce

    Would mixing php + Qt going to make the codes become harder to maintain?

  11. #8
    Join Date
    Jan 2006
    Location
    Graz, Austria
    Posts
    8,416
    Thanks
    37
    Thanked 1,544 Times in 1,494 Posts
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: How could I connect to MYSQL server if that server do not allowed any remote acce

    Not necessarily.

    It will mostly depend on the skill set of the involved people. If there are skilled PHP developers as well as skilled C++ developers each side should not have any problems maintaining their part.

    Cheers,
    _

  12. The following user says thank you to anda_skoa for this useful post:

    stereoMatching (29th January 2014)

  13. #9
    Join Date
    Jan 2011
    Posts
    127
    Thanks
    42
    Thanked 4 Times in 4 Posts
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: How could I connect to MYSQL server if that server do not allowed any remote acce

    Quote Originally Posted by anda_skoa View Post
    No, this is one option.

    Additional to the web service approach already mentioned there is also the option of splitting the Qt application in frontend (client) and backend (server).
    Or connecting through a VPN, an SSH tunnel, etc,

    Cheers,
    _
    After google(VPN or SSH), the connection could become more secure, but the port still need to be opened
    The admin worry that the server may suffer port attack if he open the port

    Back to the php and c++, how could I use php as backend?
    Create a QWebview, access the website, query the data by php, and show the data by Qt?

  14. #10
    Join Date
    Jul 2011
    Location
    Italy
    Posts
    24
    Thanks
    2
    Thanked 4 Times in 4 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows Symbian S60 Maemo/MeeGo

    Default Re: How could I connect to MYSQL server if that server do not allowed any remote acce

    Quote Originally Posted by stereoMatching View Post
    Create a QWebview, access the website, query the data by php, and show the data by Qt?
    No.
    Use QNetworkAccessManager to make the request to the server (GET or POST), read from QNetworkReply and parse the data returned (xml or json).

  15. The following user says thank you to sakya for this useful post:

    stereoMatching (29th January 2014)

  16. #11
    Join Date
    Jan 2011
    Posts
    127
    Thanks
    42
    Thanked 4 Times in 4 Posts
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: How could I connect to MYSQL server if that server do not allowed any remote acce

    Quote Originally Posted by sakya View Post
    No.
    Use QNetworkAccessManager to make the request to the server (GET or POST), read from QNetworkReply and parse the data returned (xml or json).
    According to your suggestions, we could
    1 : use php to query, update db and generate xml or json
    2 : use QNetworkAccessManager to post the request, ask the server generate json
    and insert the data into the model
    3 : update the data by posting request by QNetworkAccessManager

    Maybe not so difficult.

  17. #12
    Join Date
    Jan 2006
    Location
    Graz, Austria
    Posts
    8,416
    Thanks
    37
    Thanked 1,544 Times in 1,494 Posts
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: How could I connect to MYSQL server if that server do not allowed any remote acce

    Quote Originally Posted by stereoMatching View Post
    After google(VPN or SSH), the connection could become more secure, but the port still need to be opened
    Open as in make MySQL listen for TCP connections or open as in open for outside connections?

    Because the second is obviously not true, it would only allow internal connections in the VPN or SSH case.

    You will always need some way to connect into your network, either through HTTPS, VPN or SSH.
    The last two allow you to tunnel the database connection.

    A fourth alternative is to use a SSL connection directly between a server component and a client component.

    Cheers,
    _

  18. #13
    Join Date
    Jan 2011
    Posts
    127
    Thanks
    42
    Thanked 4 Times in 4 Posts
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: How could I connect to MYSQL server if that server do not allowed any remote acce

    Quote Originally Posted by anda_skoa View Post
    Open as in make MySQL listen for TCP connections or open as in open for outside connections?

    Because the second is obviously not true, it would only allow internal connections in the VPN or SSH case.

    You will always need some way to connect into your network, either through HTTPS, VPN or SSH.
    The last two allow you to tunnel the database connection.

    A fourth alternative is to use a SSL connection directly between a server component and a client component.

    Cheers,
    _
    Don't know what is the meaning of "open as in open for outside connections".
    But I find this solution--http://stackoverflow.com/questions/1...-remote-access

    Looks like there are more than one way to allow the computer of the local network to access the local server without open a port
    1 : open as in open for outside connections(VPN or SSH)
    2 : ssl
    3 : use php to query and update db, Qt do another job

  19. #14
    Join Date
    Jan 2011
    Posts
    127
    Thanks
    42
    Thanked 4 Times in 4 Posts
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: How could I connect to MYSQL server if that server do not allowed any remote acce

    OS : win7 64bits
    Qt : 5.2.1
    WAMP :

    Quote Originally Posted by anda_skoa View Post
    No, this is one option.

    Additional to the web service approach already mentioned there is also the option of splitting the Qt application in frontend (client) and backend (server).
    Or connecting through a VPN, an SSH tunnel, etc,

    Cheers,
    _
    Recently I give this solution a try

    read_data.php
    Qt Code:
    1. <?php
    2. $con = mysqli_connect($_POST['db_server'], $_POST['db_user'],
    3. $_POST['db_password'], $_POST['db_database']) or die(mysql_error());
    4.  
    5. if (mysqli_connect_errno()){
    6. mysqli_close($con);
    7. echo "Failed to connect to MySQL: " . mysqli_connect_error();
    8. }
    9.  
    10. $result = mysqli_query($con, $_POST['query_message']);
    11. if (mysqli_num_rows($result) > 0) {
    12. $response["results"] = array();
    13. $field = array();
    14. while ($property = mysqli_fetch_field($result)) {
    15. $field[] = $property->name;
    16. //echo $property->name .";";
    17. }
    18. //echo(count($field));
    19.  
    20. while ($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
    21. $table_data = array();
    22. //echo(count($row) .";");
    23. for ($i = 0; $i < count($row); ++$i) {
    24. $table_data[$field[$i]] = $row[$i];
    25. }
    26. // push single temp into final response array
    27. array_push($response["results"], $table_data);
    28. }
    29. // success
    30. $response["success"] = 1;
    31.  
    32. // echoing JSON response
    33. echo json_encode($response);
    34. }else{
    35. // no products found
    36. $response["success"] = 0;
    37. $response["message"] = mysqli_error($con);
    38. // echo no users JSON
    39. echo json_encode($response);
    40. }
    41. ?>
    To copy to clipboard, switch view to plain text mode 

    main.cpp
    Qt Code:
    1. #include "MainWindow.hpp"
    2. #include <QApplication>
    3.  
    4. #include <QDebug>
    5. #include <QByteArray>
    6. #include <QEventLoop>
    7. #include <QNetworkReply>
    8. #include <QTextCodec>
    9. #include <QUrlQuery>
    10.  
    11. int main(int argc, char *argv[])
    12. {
    13.  
    14. QString const &DB_SERVER = "1xx.1xx.1xx.1xx";
    15. QNetworkRequest request("http://" + "DB_SERVER" + "/bridge/read_data.php");
    16. request.setHeader(QNetworkRequest::ContentTypeHeader,
    17. "application/x-www-form-urlencoded");
    18.  
    19. QUrlQuery query;
    20. query.addQueryItem("db_database", "student_sms");
    21. query.addQueryItem("db_password", "1234");
    22. query.addQueryItem("db_server", DB_SERVER);
    23. query.addQueryItem("db_user", "root");
    24. query.addQueryItem("query_message", "select * from student_sms_basic");
    25.  
    26. QNetworkAccessManager network_manager;
    27. auto network_reply = network_manager.post(request,
    28. query.query().toUtf8());
    29.  
    30. QEventLoop loop;
    31. connect(network_reply, SIGNAL(finished()), &loop, SLOT(quit()));
    32. loop.exec();
    33.  
    34. QByteArray raw_data;
    35. if(network_reply->error() == QNetworkReply::NoError){
    36. raw_data = network_reply->readAll();
    37. }else{
    38. qDebug()<<network_reply->errorString();
    39. }
    40.  
    41. qDebug()<<QTextCodec::codecForHtml(raw_data)->toUnicode(raw_data);
    42.  
    43. }
    To copy to clipboard, switch view to plain text mode 

    I can query the data from local(server == 127.0.0.1) but can't query the data if I try
    to access the server remotely(access deny). What kind of error I make?

  20. #15
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: How could I connect to MYSQL server if that server do not allowed any remote acce

    It's probably insufficient permissions to access the MySql database from the PHP running on web server using the credentials you supplied. I am only guessing... you provided no information to work from.

  21. #16
    Join Date
    Jan 2011
    Posts
    127
    Thanks
    42
    Thanked 4 Times in 4 Posts
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: How could I connect to MYSQL server if that server do not allowed any remote acce

    Quote Originally Posted by ChrisW67 View Post
    It's probably insufficient permissions to access the MySql database from the PHP running on web server using the credentials you supplied. I am only guessing... you provided no information to work from.
    Sorry for that, I forgot to post the error message.

    error messages :

    ( ! ) Warning: mysqli_connect(): (HY000/1130): Host 'luke-PC' is not allowed to connect to this MySQL server in C:\wamp\www\bridge\read_data.php on line 3


    Added after 1 7 minutes:


    Solved(partially).

    What I need to do is create a new user and granted the user privileges to access from remote pc.

    something like this

    Qt Code:
    1. GRANT ALL PRIVILEGES ON student_sms TO 'testUser'@'%' IDENTIFIED BY '1234';
    To copy to clipboard, switch view to plain text mode 

    Pretty easy yet confuse to beginners like me. Don't know why the root can't access from remote pc?
    Last edited by stereoMatching; 11th March 2014 at 04:01.

Similar Threads

  1. Connect to the MYSQL server successful but can't see any data
    By stereoMatching in forum Qt Programming
    Replies: 1
    Last Post: 9th January 2014, 05:06
  2. Replies: 2
    Last Post: 16th April 2012, 14:42
  3. Replies: 3
    Last Post: 8th March 2011, 08:57
  4. QFtp: downloading a whole folder from a remote server.
    By balazsbela in forum Qt Programming
    Replies: 5
    Last Post: 5th August 2007, 10:34
  5. Acces to a remote mysql server
    By Alienxs in forum Qt Programming
    Replies: 2
    Last Post: 19th August 2006, 04:10

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.