Solved

Can I Talk to a mysql database over secure SSL connection in a Plesk VPS enviroment?

Posted on 2009-05-07
11
670 Views
Last Modified: 2012-05-06
Hi

Is it possible to talk/interact with a mysql database over a SSL connection?

We run multiple domains on a VPS server setup with Plesk, so I assume we have 1 Mysql Server which hosts all our databases.

To give a bit more detail on what we are trying to achieve, we have a main site http://www.domain.com which talks to DB x and we need https://www.domain.com to be able to talk to the same database x.

Any input would be much appriciated.

Thanks
--s--
0
Comment
Question by:socross
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 29

Accepted Solution

by:
fosiul01 earned 500 total points
Comment Utility
Basic is Yes

if you buy a SSl certificate, and implemented in your Server

any transaction goes via https:// sign its secure

https:// is there to encrypt your data over http.

0
 
LVL 92

Expert Comment

by:objects
Comment Utility
you can achieve that using an ssl tunnel and have the database access done over the tunnel

0
 
LVL 1

Author Comment

by:socross
Comment Utility
Ok Great

I am currently developing and am working on the secure files (httpsdocs) fine, but any connections to my database return empty objects!

Is it possible to configure access to my database, securely via https:// and standard via http://

objects - could you provide a bit more infor on how this would work and any resources on how to set it up

Many thanks

--s--
0
 
LVL 92

Expert Comment

by:objects
Comment Utility
0
 
LVL 29

Assisted Solution

by:fosiul01
fosiul01 earned 500 total points
Comment Utility
I guess @author is asking about accessing the mysql via web site . as far i know Ssh tunnel is for for access mysql server between 2 server.

about coding

i can give you some idea about php

suppose i have some credit card transaction which is stored in mysql table

now if i do this

http://mydomain.com/insertcredit.php

now all the credit card information will insert to my mysql database but it will be wide open for every one

but if i do
https://mydomain.com/insertcredit.php

it will go to same mysql database  but data would not be open to any one because it going via secure encrypted channel.

so if you want secure transcation use ssl

Now come to point :

from coding point of view, i would of use same php code for http and https

here you dont have to write any special code for https,
you just have to switch the web site for secure page from http to https
if your code works for http it will work for https aswell

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:socross
Comment Utility
fosiul01:
Perfect Ok & Thats what i was hoping.

But when using MYSQLI to connect to my database I am getting empty results even though the querys are working (Tested using phpMyAdmin)

See Code snippet for example, although it conectts to the db it returns an empty object and returns 0.

Am i missing something

--s--
  	// connect to db

  	$conn = db_connect(); 

        

        // It Connects ok.

	if (!$conn)

     	    return 0;

	else

            echo 'connection success';

		

	 // check username

	 $result = $conn->query("select LCASE($var) from $table where LCASE($var) = LCASE('$value');");
 

	if (!$result)

	{

	    echo 'object not created';

	    return 0;

	}
 

	if ($result->num_rows>0)

		return 1;

	else 

		return 0;

Open in new window

0
 
LVL 29

Expert Comment

by:fosiul01
Comment Utility
@author

i would of suggest you to create another question for this as its coding problem,

create another quesaton in php zone, you will get more and accurate answer.


i did  not touch php programming from last 6 to 7 month, so almost forgot the syntax....
sorry for this that would be able to help you for coding part.
0
 
LVL 33

Expert Comment

by:Dave Howe
Comment Utility
ok, I think we are at cross purposes here.

Assume setup:

[CLIENT] -- LINK1 --> [WEBSERVER] -- LINK2 --> [DB-HOST]

There are two links involved - normally, WEBSERVER and DB-HOST are the same, so you don't need to worry about DB security. in a cluster, you may also find that an interserver link is effectively secure (as in, on a private network) so that LINK2 is insecure, but in an isolated environment so no real risk.

On the webserver, there should be a HTTPS certificate - this is used to secure LINK1 *only* - the backend link LINK2 is not secured in any way by the server certificate, so you should treat these as completely separate problems. In most situations though, you can assume that your LINK2 is secure unless there is a valid reason to assume otherwise.

if you still want to secure LINK2, there are three layers of technology that can achieve this for you - these are
1) SERVICE encryption
MySQL natively supports/allows SSL encryption. to make this work, you must supply a certificate+key to the server, and configure the client to use ssl
Upside: everything is handled natively.
Downside: you have to reconfigure your code to use a secure connection (its asserted clientside) and maintain a certificate for the use of the MySQL server.

MySQL official documentation --> http://dev.mysql.com/doc/refman/5.0/en/secure-connections.html

2) TUNNEL encryption
Tunnel mode programs allow you to define a local port on a machine without MySQL to be linked (via encryption) to a port on the MySQL server.
Upside: If you use the same port, you can just define the MySQL server as localhost (on all servers) and it doesn't matter which node a website is running on; localhost is always the same instance of MySQL.
Downside: reliance on secondary code (the tunnel software); ssl based tunnel software (such as stunnel) requires maintaining a certificate for the MySQL server (if that style of tunnel is used); all connections appear to be "from" localhost, so invalidating host based permissions.

SSL Based tunnel solution ---> http://www.stunnel.org/

3) VPN encryption
VPN (virtual private network) programs allow each node in a group to be assigned a "private" IP address (192.168.20.x say) and communications between them are automatically encrypted and routed

Upside: can be used for *anything*, not just MySQL, and all the traffic is secure; host based permissions work, logfiles reflect the private IP (but are accurate), some VPN capabilities are built into most modern operating systems (Windows and Linux both have kernel-level support for IPSec, the non-SSL based, "standard" flavour of vpn)
Downside: reliance on secondary code (vpn software); maintaining a SSL certificate or other "shared secret"

Popular SSL based vpn solution --> http://openvpn.net/
0
 
LVL 33

Expert Comment

by:Dave Howe
Comment Utility
ok, I think we are at cross purposes here.

Assume setup:

[CLIENT] -- LINK1 --> [WEBSERVER] -- LINK2 --> [DB-HOST]

There are two links involved - normally, WEBSERVER and DB-HOST are the same, so you don't need to worry about DB security. in a cluster, you may also find that an interserver link is effectively secure (as in, on a private network) so that LINK2 is insecure, but in an isolated environment so no real risk.

On the webserver, there should be a HTTPS certificate - this is used to secure LINK1 *only* - the backend link LINK2 is not secured in any way by the server certificate, so you should treat these as completely separate problems. In most situations though, you can assume that your LINK2 is secure unless there is a valid reason to assume otherwise.

if you still want to secure LINK2, there are three layers of technology that can achieve this for you - these are
1) SERVICE encryption
MySQL natively supports/allows SSL encryption. to make this work, you must supply a certificate+key to the server, and configure the client to use ssl
Upside: everything is handled natively.
Downside: you have to reconfigure your code to use a secure connection (its asserted clientside) and maintain a certificate for the use of the MySQL server.

MySQL official documentation --> http://dev.mysql.com/doc/refman/5.0/en/secure-connections.html

2) TUNNEL encryption
Tunnel mode programs allow you to define a local port on a machine without MySQL to be linked (via encryption) to a port on the MySQL server.
Upside: If you use the same port, you can just define the MySQL server as localhost (on all servers) and it doesn't matter which node a website is running on; localhost is always the same instance of MySQL.
Downside: reliance on secondary code (the tunnel software); ssl based tunnel software (such as stunnel) requires maintaining a certificate for the MySQL server (if that style of tunnel is used); all connections appear to be "from" localhost, so invalidating host based permissions.

SSL Based tunnel solution ---> http://www.stunnel.org/

3) VPN encryption
VPN (virtual private network) programs allow each node in a group to be assigned a "private" IP address (192.168.20.x say) and communications between them are automatically encrypted and routed

Upside: can be used for *anything*, not just MySQL, and all the traffic is secure; host based permissions work, logfiles reflect the private IP (but are accurate), some VPN capabilities are built into most modern operating systems (Windows and Linux both have kernel-level support for IPSec, the non-SSL based, "standard" flavour of vpn)
Downside: reliance on secondary code (vpn software); maintaining a SSL certificate or other "shared secret"

Popular SSL based vpn solution --> http://openvpn.net/
0
 
LVL 33

Expert Comment

by:Dave Howe
Comment Utility
oops - site glitch - got "down for unexpected maintainance" then two copies turn up....
0
 
LVL 1

Author Comment

by:socross
Comment Utility
Thanks for all your input, after all your feedback and reading on the next I have basically gone on a bit of a wild goose chase, as it was bad php code which caused the issue!!

Thanks for helping me get to the bottom of this and for all your input!

Best

--s--
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

It is possible to boost certain documents at query time in Solr. Query time boosting can be a powerful resource for finding the most relevant and "best" content. Of course the more information you index, the more fields you will be able to use for y…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now