Solved

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

Posted on 2009-05-07
11
736 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 29

Accepted Solution

by:
fosiul01 earned 500 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 92

Expert Comment

by:objects
ID: 24324147
0
 
LVL 29

Assisted Solution

by:fosiul01
fosiul01 earned 500 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Building a glossary into your website 9 59
Get data from two MySQL tables 6 66
MySql Linux vs Windows: bad results for Windows but why? 10 72
ssl mixed content reported 1 22
In Solr 4.0 it is possible to atomically (or partially) update individual fields in a document. This article will show the operations possible for atomic updating as well as setting up your Solr instance to be able to perform the actions. One major …
Creating and Managing Databases with phpMyAdmin in cPanel.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

739 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