0

I'm trying to extract a specific dataset via a daily cronjob written in PHP (by extracting the data from a MariaDB DB), and transfer it onto another server D which is externally available to a client. The approach I'm using is:

A) Extract data from MariaDB DB Table via local cronjob PHP file. As this file is stored on the same server as the one hosting the DB (server A), guess there's not much to worry about in terms of security, as the DB is accessed via the localhost.

B) Write the in step A) extracted data to a CSV file located on the server A.

C) Copy the CSV file created in B) onto the server D.

What I'm concerned about is securely transferring the csv to server D. The data should be encrypted and there should be mutual authentication.

The options I see to ensure security is:

  1. Use ftp_ssl_connect(), which however fails with the error : PHP Warning: ftp_login(): Server not configured for encryption.

So I guess I would somehow need to enable the SSL certificate encryption on the side of server D? which seems weird to me, I thought this was default with a server where SSL is enabled.

  1. Use symmetric encryption + asymmetric encryption of the symmetric key (mainly to reach unlimited message length in terms of encryption, which is not possible with asymmetric encryption as far as I know). This however seems like overshooting x100000 to me; and encrypting and decrypting a csv - file holding thousands of rows may not be really performant.

  2. Use the PHP extension phpseclib to transfer the file across an SFTP connection.

What is your best shot in terms of server security + transfer performance among these 3 options?

10
  • 2
    "the security during the transfer" -- what do you want to secure against? What threats are there? #1 is a plain ol' config issue. You've described SSL in #2, which would be provided once you configure the server properly. Performance considerations are beyond the scope of this site. So, I guess, I'm asking, "what's your question?"
    – schroeder
    Nov 3 '21 at 9:56
  • Just set up SFTP on server D.
    – schroeder
    Nov 3 '21 at 9:57
  • It could be summed up to "what's the safest way to transfer the csv file from server A to server D", among the 3 provided options?
    – DevelJoe
    Nov 3 '21 at 9:58
  • Again, "safest" in what sense? What do you want to secure against? Are you just asking how to do an encrypted transfer?
    – schroeder
    Nov 3 '21 at 9:59
  • 2
    So, this is looking like you tried ftp_login(), got an error that you didn't understand, and instead of resolving the error, you want to look at a wide range of different options for secure file transfer from first principles. What if you just configured server D to resolve the error?
    – schroeder
    Nov 3 '21 at 10:15
2

TL;DR: Either your first (FTPS) or third (SFTP) options are sound choices. There are some minor security differences between them, depending on things like whether you know the server D's public key for SFTP or whether you can use public key authentication (or TLS mutual authentication) instead of the slightly weaker password authentication, but either approach is quite adequate. There are lots of ways to do this, but either of those are probably the best bets for this situation.


Use ftp_ssl_connect(), which however fails with the error : PHP Warning: ftp_login(): Server not configured for encryption.

That just sounds like you haven't actually configured SSL/TLS on your FTP server... or at least, you haven't configured it the way PHP (Why, of all things, PHP?!?) expects. The original FTP specification doesn't support secure connections at all, and there are a few different ways to retrofit it into the protocol (sometimes called FTPS, by analogy to HTTPS and very distinct from SFTP which is a completely different protocol over SSH instead of SSL/TLS). Obviously, make sure the FTP server on D is configured with a trusted certificate, but if you have such a certificate plus some method of client authentication (probably username+password, since it's FTP), that should work fine.

Use symmetric encryption + asymmetric encryption of the symmetric key (mainly to reach unlimited message length in terms of encryption, which is not possible with asymmetric encryption as far as I know). This however seems like overshooting x100000 to me; and encrypting and decrypting a csv - file holding thousands of rows may not be really performant.

As schroeder's comment says, you just described the core of SSL/TLS and also SSH (used for SFTP), at least when doing RSA key exchange ([EC]DHE key exchanges - favored because they provide forward secrecy - are slightly more complicated). You also described OpenPGP while you're at it.

You absolutely should not try to re-invent those protocols - they are complicated, tricky beasts, and lots of mistakes have been made in both protocol design and in implementations, even by people much more experienced than you or I - but you have the basic idea right. Your biggest error here (beyond not realizing that you're reinventing the wheel without considering important questions like public key distribution) is thinking that it's going to be some kind of performance disaster. That exact type of process happens every time you load a web page over HTTPS, or fetch your email over IMAP4 + STARTTLS, or SFTP a file, or... you get the idea. You're not going to avoid that performance impact, but it's also way smaller than you seem to think it is.

If you did want to go this route (encrypting the file before transmitting it over an insecure network protocol such as plain FTP), you could use something conforming to OpenPGP, such as GnuPG (commonly gpg). I don't recommend it though; lots of complexity for no point.

Use the PHP extension phpseclib to transfer the file across an SFTP connection.

If you have an SFTP server on server D, this might be the simplest option. Do make sure you know its public key, though, and that it's being verified. SSH (which SFTP uses for security) is generally trust-on-first-use and remembers the key the first time you use it, but that requires not being subject to a MitM attack at first use. Consider using public key authentication rather than username+password authentication if you can.


Just for completion, a few other options:

Have an HTTPS web server on D, configured to accept authenticated file uploads, and upload the file as/in the body of an authenticated HTTPS POST or PUT request. There's really no point setting this up if you don't already have an HTTPS server on D with an upload endpoint that is easy to interact with programmatically (i.e. it doesn't require a separate login step that provides a cookie), but it would work fine.

Establish an SSH connection for port forwarding to the FTP port(s) on D, and then FTP your file to the locally forwarded port on localhost. It will look like you're FTPing it over an insecure connection to a local FTP server, but actually you're sending it over a secure connection to the FTP server on D (this is called FTP over SSH). This is strictly more complicated than just using SFTP though, so don't.

Invoke a subprocess (sftp or ftp or curl or whatever) to transfer the file using a tool that's actually built for this, instead of using a web application framework originally designed for serving personal home pages. Of course, if you're doing this, you probably wouldn't want to be using PHP for the cronjob at all (still confused why you are; a simple shell script invoking the MariaDB command line client and one of the tools above would probably work fine?). The main danger with using subprocesses here is making sure that there's no risk of command injection (or shell injection if for some reason you use a shell) due to e.g. improperly escaped malicious file names. That shouldn't be a problem, since you control the file name. On the other hand, it probably buys you nothing over just using PHP modules.

11
  • Thx a lot for your very extensive answer. I was completely unaware that TLS/SSL / SSH work on the principle described in point 2, I mainly came across this idea due to this forum post here, which I will in this case forget about :)
    – DevelJoe
    Nov 3 '21 at 12:25
  • Did some research, so to understand you correctly: by "public key authentication", you mean connecting to another server by sending the public key to it, together with a signature which has been created on the side of the requesting client, using the private key which is associated to the public key? Am I getting you right? So that's basically what this here does, do you know that?
    – DevelJoe
    Nov 3 '21 at 13:59
  • 1
    You are correct on all fronts, except that I would use the ssh-keygen command-line tool (rather than using phpseclib) for generating the keypair. I also recommend password-protecting the private key (though the password will be relatively exposed). The SSH key format is generally interchangeable among implementations. Putting the public key on D and enabling SSH login is generally done by adding the public key to the ~/.ssh/authorized_keys file for the relevant user on D, and making sure that the relevant port (22 by default) is exposed to the client.
    – CBHacking
    Nov 3 '21 at 17:50
  • 1
    In an ideal world you'd use a hardware security module (HSM), or a key management service backed by an HSM, but realistically just storing the private key in its default location (~/.ssh/id_<alg> for SSH clients, /etc/ssh/ssh_host_<alg>_key` on SSH servers) is probably fine. Obviously you want the key to be stored outside any webroot or similar and not available through the server. The sshd (SSH server) host key should already have been created the first time sshd starts. The ssh client key is generated by ssh-keygen and by default uses the path under the home directory (~/.ssh/...)
    – CBHacking
    Nov 3 '21 at 22:20
  • 1
    And a last follow-up question: both servers are part of the same network, would you chabge the approach knowing that? In my eyes, I'd still stick to SFTP over an SSH channel, opened using public key authentication, right?
    – DevelJoe
    Nov 4 '21 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.