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?

schroeder
  • 123,438
  • 55
  • 284
  • 319
DevelJoe
  • 115
  • 4
  • 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 03 '21 at 09:56
  • Just set up SFTP on server D. – schroeder Nov 03 '21 at 09: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 03 '21 at 09: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 03 '21 at 09:59
  • Basically yes, which of the 3 options is the most feasible one to do an encrypted transfer? Just to make sure that a man-in-the-middle cannot read the transfered data, and that the identity of the sender of the transfer is always verified. I'm just unsure to which extent these two aspects are assured using SFTP; is that enough? – DevelJoe Nov 03 '21 at 10:03
  • Thx for your corrections. Oh and also, if you may know why I get the error ```PHP Warning: ftp_login(): Server not configured for encryption.``` . That could help, and would avoid the need of an SFTP PHP extension. – DevelJoe Nov 03 '21 at 10:11
  • 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 03 '21 at 10:15
  • Well I don't know how to configure server D to resolve the error (I'm not even sure of getting the error, as my experience in this area is low). What I know for sure is that the transfer succeeded when using ```ftp_connect()``` instead of ```ftp_ssl_connect()```, so I supposed the error's due to some failed SSL authentication. I've however already used SFTP transfers, and if SFTP encrypts transferred files by default, that's a possibility too for me. – DevelJoe Nov 03 '21 at 10:22
  • Also, SFTP implementations seem to be of a much lower risk than FTPS, considering the needed opening of multiple ports, according to [this](https://www.goanywhere.com/blog/2011/10/20/sftp-ftps-secure-ftp-transfers), but not sure..? – DevelJoe Nov 03 '21 at 10:24
  • What matters here is *what is available on the server and on the client?*. If you have a sshd daemon on the server and a sftp on the client, then it is the way to go, because sftp is a well known and robust protocol with well known implementations. FPTS would be an acceptable choice but it is much to correctly set up. But IMHO php may not be the best tool to encapsulate that: I would use a scripting language like bourne shell (Linux), Power Shell (Windows) or Python (multi-platform). – Serge Ballesta Nov 03 '21 at 11:22

1 Answers1

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.

CBHacking
  • 40,303
  • 3
  • 74
  • 98
  • 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](https://www.sitepoint.com/encrypt-large-messages-asymmetric-keys-phpseclib/), which I will in this case forget about :) – DevelJoe Nov 03 '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](https://phpseclib.com/docs/auth/#public-key) does, do you know that? – DevelJoe Nov 03 '21 at 13:59
  • Concretely, this means that I would need to place the public key of SSH user x on server D, and enable server D to use the SSH protocol for user x, and then establish every SFTP connection of phpseclib using [this](https://phpseclib.com/docs/auth/#public-key), as the SFTP class extends the SSH class. Correct? – DevelJoe Nov 03 '21 at 14:45
  • And the public key I would need to create on my own through the creation of a new key pair using phpseclib, right? And of that newly created key pair, I store the obtained public key on the server D and enable the username_x ssh access to server D on server D, correct? – DevelJoe Nov 03 '21 at 16:48
  • 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 03 '21 at 17:50
  • And just out of curiosity, what's ur preferred way of storing the private key? Just in a separate file at the servers root? – DevelJoe Nov 03 '21 at 21:13
  • 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_` for SSH clients, /etc/ssh/ssh_host__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 03 '21 at 22:20
  • Ok, Cheers mate! – DevelJoe Nov 03 '21 at 22:30
  • 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 04 '21 at 17:55
  • And another last one :) If a hacker like a MITM somehow manages to get access to the transmitted public key for connection, couldn't he then theoretically spoof the server D's target IP on subsequent requests and then catch + decrypt files that are transferred across that channel? – DevelJoe Nov 05 '21 at 09:01
  • @DevelJoe Answer is a bit long for a comment - I recommend looking up "SSH server authentication" and asking a new question if you're still confused - but the short answer is "usually, no". It does depend on the client's behavior, though, and I don't know how phpseclib handles SSH server auth. – CBHacking Nov 05 '21 at 21:22