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

[stereoMatching]

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.

[ChrisW67]

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.

[stereoMatching]

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?

[ChrisW67]

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 (http://lmgtfy.com/?q=php+development+framewworks)
Nothing is nearly as drag-and-drop as Designer. There are also Microsoft .Net, Java, Python, Ruby and other options.

[sakya]

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.

[anda_skoa]

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,
_

[stereoMatching]

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

[anda_skoa]

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,
_

[stereoMatching]

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?

[sakya]

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).

[stereoMatching]

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.

[anda_skoa]

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,
_

[stereoMatching]

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/14295287/how-to-allow-mysql-on-windows-server-for-external-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

[stereoMatching]

OS : win7 64bits
Qt : 5.2.1
WAMP :


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


<?php
$con = mysqli_connect($_POST['db_server'], $_POST['db_user'],
$_POST['db_password'], $_POST['db_database']) or die(mysql_error());

if (mysqli_connect_errno()){
mysqli_close($con);
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con, $_POST['query_message']);
if (mysqli_num_rows($result) > 0) {
$response["results"] = array();
$field = array();
while ($property = mysqli_fetch_field($result)) {
$field[] = $property->name;
//echo $property->name .";";
}
//echo(count($field));

while ($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
$table_data = array();
//echo(count($row) .";");
for ($i = 0; $i < count($row); ++$i) {
$table_data[$field[$i]] = $row[$i];
}
// push single temp into final response array
array_push($response["results"], $table_data);
}
// success
$response["success"] = 1;

// echoing JSON response
echo json_encode($response);
}else{
// no products found
$response["success"] = 0;
$response["message"] = mysqli_error($con);
// echo no users JSON
echo json_encode($response);
}
?>


main.cpp


#include "MainWindow.hpp"
#include <QApplication>

#include <QDebug>
#include <QByteArray>
#include <QEventLoop>
#include <QNetworkReply>
#include <QTextCodec>
#include <QUrlQuery>

int main(int argc, char *argv[])
{

QString const &DB_SERVER = "1xx.1xx.1xx.1xx";
QNetworkRequest request("http://" + "DB_SERVER" + "/bridge/read_data.php");
request.setHeader(QNetworkRequest::ContentTypeHead er,
"application/x-www-form-urlencoded");

QUrlQuery query;
query.addQueryItem("db_database", "student_sms");
query.addQueryItem("db_password", "1234");
query.addQueryItem("db_server", DB_SERVER);
query.addQueryItem("db_user", "root");
query.addQueryItem("query_message", "select * from student_sms_basic");

QNetworkAccessManager network_manager;
auto network_reply = network_manager.post(request,
query.query().toUtf8());

QEventLoop loop;
connect(network_reply, SIGNAL(finished()), &loop, SLOT(quit()));
loop.exec();

QByteArray raw_data;
if(network_reply->error() == QNetworkReply::NoError){
raw_data = network_reply->readAll();
}else{
qDebug()<<network_reply->errorString();
}

qDebug()<<QTextCodec::codecForHtml(raw_data)->toUnicode(raw_data);

}


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?

[ChrisW67]

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.

[stereoMatching]

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



GRANT ALL PRIVILEGES ON student_sms TO 'testUser'@'%' IDENTIFIED BY '1234';


Pretty easy yet confuse to beginners like me. Don't know why the root can't access from remote pc?