Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-05-07
11
Medium Priority
?
819 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 2000 total points
ID: 24324080
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
ID: 24324094
you can achieve that using an ssl tunnel and have the database access done over the tunnel

0
 
LVL 1

Author Comment

by:socross
ID: 24324136
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 92

Expert Comment

by:objects
ID: 24324147
0
 
LVL 29

Assisted Solution

by:fosiul01
fosiul01 earned 2000 total points
ID: 24324195
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
 
LVL 1

Author Comment

by:socross
ID: 24324291
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
ID: 24324396
@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
ID: 24324420
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
ID: 24324427
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
ID: 24324438
oops - site glitch - got "down for unexpected maintainance" then two copies turn up....
0
 
LVL 1

Author Comment

by:socross
ID: 24324457
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

926 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