Posted in PHP

In a recent project I had to download and process a bunch of CSVs. Initially I had an extremely ugly exec() call to linux’s wget command for reasons I won’t go into but obviously a better, PHP-based solution was required. I had previous experience with Guzzle and its pooled requests so it was the obvious place to go.

Below is the script I ended up with. It takes an array of files and downloads them all to a __FILE__.’/downloads/’ directory. Not the cleanest thing in the world but it did the trick and you should be able to adapt it as you need.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
<?php
 
ini_set('display_errors', true);
require __DIR__.'/vendor/autoload.php';
 
use GuzzleHttp\Client;
use GuzzleHttp\Pool;
use GuzzleHttp\Psr7\Response;
 
$files = array(
	'http://mysite.com/1.csv',
	'http://mysite.com/2.csv',
	'http://mysite.com/3.csv',
	'http://mysite.com/4.csv',
);
 
// Track redirects so our Pool's fulfilled closure knows which URL the current
// download is for.
$client = new Client([
	'allow_redirects' => ['track_redirects' => true]
]);
 
$requests = function($total) use ($client, $files, $import_path) {
    foreach ( $files as $file )
    {
        yield function($poolOpts) use ($client, $file) {
            $reqOpts = array_merge($poolOpts, [
            	// Sink option specifies the download path for this file
                'sink' => __DIR__.'/downloads/' . basename($file)
            ]);
 
            return $client->getAsync($file, $reqOpts);
        };
    }
};
 
$pool = new Pool($client, $requests(100), [
    'concurrency' => 3,
    'fulfilled' => function(Response $response, $index) use ($files) {
        // Grab the URLs this file redirected through to download in chronological order.
        $urls = $response->getHeader(\GuzzleHttp\RedirectMiddleware::HISTORY_HEADER);
 
        echo "Downloaded ", end($urls), "<br/>\n";
    },
    'rejected' => function(Exception $reason, $index) use (&$import_errors) {
        $url = (string)$reason->getRequest()->getUri();
 
        echo "Failed to download ", $url, ": ", $reason->getMessage(), "<br/>\n";
    },
]);
 
$pool->promise()->wait();
 
echo "Finished downloading.<br/>";

Read More »

Posted (Updated ) in Database, Linux, PHP

After suffering some pretty bad issues with MAMP, I decided to set everything up with homebrew instead. The result was surprisingly a much faster and (in my opinion) easier to configure setup.

As a tl;dr, we’ll be setting up Homebrew MySQL and PHP and using OSX’s built in Apache.

In this tutorial I’m using the subl command which will open a file for editing in Sublime Text. If you don’t use Sublime Text, replace subl with nano or vi or any other app you use to edit text/config files.

 

Homebrew Setup

Homebrew is a package manager for OSX. It makes installation of a wide variety of useful apps super easy.

Installation instructions are on the homebrew homepage but you can also just run the following:

1
/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

 

MySQL

I lied! We’re installing MariaDB instead! At the time of writing MySQL version 8.0.11 has just changed its default authentication method to caching_sha2_password which isn’t supported in PHP. It’s a huge hassle so we’ll just use the drop-in replacement MariaDB instead.

Install and configure MariaDB.

1
2
3
4
# Install MariaDB
brew install mariadb
# Open my.cnf config file for editing
subl /usr/local/etc/my.cnf

Add the following to the end of the file to add support for large imports:

1
2
max_allowed_packet = 2G
innodb_file_per_table = 1

Make MySQL start when you log in:

1
brew services start mariadb

The default installation comes with a passwordless root user. So secure it with:

1
mysql_secure_installation

 

SSL

Like all developers I like working on a custom subdomain – in this case localhost.com. We need to create a self-signed wildcard SSL certificate and get Chrome accepting it.

Create a folder /Users/your_username/Sites/certs and inside it run the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# Generate a temporary OpenSSL config file
cat > openssl.cnf <<-EOF
  [req]
  distinguished_name = req_distinguished_name
  x509_extensions = v3_req
  prompt = no
  [req_distinguished_name]
  CN = *.localhost.com
  [v3_req]
  keyUsage = keyEncipherment, dataEncipherment
  extendedKeyUsage = serverAuth
  subjectAltName = @alt_names
  [alt_names]
  DNS.1 = *.localhost.com
  DNS.2 = localhost.com
EOF
 
# Generate the certificates
openssl req \
  -new \
  -newkey rsa:2048 \
  -sha1 \
  -days 3650 \
  -nodes \
  -x509 \
  -keyout ssl.key \
  -out server.crt \
  -config openssl.cnf
 
# Delete the temporary config file
rm openssl.cnf

This should have created two files – server.crt and server.key which will be used in the apache config below to get HTTPS up and running.

But first, because this certificate is self-signed, it’ll result in a This site’s security certificate is not trusted! error in Chrome. That can be fixed through adding the cert to OSX’s keychain app.

  • 1
    
    open /Applications/Utilities/Keychain\ Access.app /Users/your_username/Sites/certs/server.crt
  • Set the Keychain dropdown to System and click Add
  • Now in the Certificates section of Keychain find your newly added cert, double click it, expand the Trust section and set everything to Always Trust
  • These changes will only take effect after a browser restart.

 

Apache and PHP

OSX 10.13 High Sierra comes (at the time of writing) with Apache 2.4.33.

To configure apache (with SSL):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
# Install PHP 7.1
brew install php@7.1
brew link --overwrite --force php@7.1
# Open httpd.conf for editing
subl /etc/apache2/httpd.conf
 
# Enable the PHP and SSL modules by removing the # at the start of the line
LoadModule socache_shmcb_module libexec/apache2/mod_socache_shmcb.so
LoadModule ssl_module libexec/apache2/mod_ssl.so
LoadModule php7_module /usr/local/opt/php@7.1/lib/httpd/modules/libphp7.so
# A few extras I like to have enabled
LoadModule deflate_module libexec/apache2/mod_deflate.so
LoadModule expires_module libexec/apache2/mod_expires.so
LoadModule headers_module libexec/apache2/mod_headers.so
LoadModule rewrite_module libexec/apache2/mod_rewrite.so
 
# Point the document root to a htdocs folder in your home directory and enable .htaccess
# I've removed all the comments for succinctness but feel free to leave them in
DocumentRoot "/Users/your_username/htdocs"
<Directory "/Users/your_username/htdocs">
    Options FollowSymLinks Multiviews
    MultiviewsMatch Any
 
    AllowOverride All
 
    Require all granted
</Directory>
 
# Add PHP to your default file list
<IfModule dir_module>
    DirectoryIndex index.html index.php
</IfModule>
 
# And make it work
<FilesMatch \.php$>
    SetHandler application/x-httpd-php
</FilesMatch>
 
# As with content, we want to load all site definitions from a Sites folder in our 
# home directory. At the bottom replace the following:
# Include /private/etc/apache2/other/*.conf
IncludeOptional /Users/your_username/Sites/*.conf
 
# The error and custom logs too
CustomLog "/Users/your_username/Sites/logs/apache2/access_log" common
ErrorLog "/Users/your_username/Sites/logs/apache2/error_log"
 
# Uncomment to load the SSL config
Include /private/etc/apache2/extra/httpd-ssl.conf

Now configure the default SSL options:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# Open httpd.conf for editing
subl /etc/apache2/extra/httpd-ssl.conf
 
# Point to our same document root as before
DocumentRoot "/Users/your_username/htdocs"
 
# Update log file locations
ErrorLog "/Users/your_username/Sites/logs/apache2/error_log"
TransferLog "/Users/your_username/Sites/logs/apache2/access_log"
CustomLog "/Users/your_username/Sites/logs/apache2/ssl_request_log" \
          "%t %h %{SSL_PROTOCOL}x %{SSL_CIPHER}x \"%r\" %b"
 
# Point to the certs we created
SSLCertificateFile "/Users/your_username/Sites/certs/server.crt"
SSLCertificateKeyFile "/Users/your_username/Sites/certs/server.key"

Since this is a development machine, you’ll probably also want to enable the ever popular xdebug which luckily for us comes pre-compiled with OSX. What OSX doesn’t come with, however, is a default php.ini though it does have a sample file. We can use that:

1
sudo cp /etc/php.ini.default /etc/php.ini

Then simply add extension=xdebug.so below all the extension= lines in your new /etc/php.ini file.

VirtualHosts

I like to split virtualhosts up into one for each site and store them all in /Users/your_username/Sites/ folder.

Create a file /Users/your_username/Sites/mysite.localhost.com.conf and add the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<VirtualHost *:80>
  ServerAdmin webmaster@localhost
  ServerName mysite.localhost.com
  ServerAlias mysite.localhost.com
  DocumentRoot /Users/your_username/htdocs/mysite.com
 
  ErrorLog /Users/your_username/Sites/logs/mysite.com.error.log
  LogLevel warn
  CustomLog /Users/your_username/Sites/logs/mysite.com.access.log varnishcombined
 
  <Directory /Users/your_username/htdocs/mysite.com/>
    Options Indexes FollowSymLinks
    AllowOverride All
    Require all granted
  </Directory>
</VirtualHost>
 
<IfModule ssl_module>
  <VirtualHost *:443>
    ServerAdmin webmaster@localhost
    ServerName mysite.localhost.com
    ServerAlias mysite.localhost.com
    DocumentRoot /Users/your_username/htdocs/mysite.com
 
    ErrorLog /Users/your_username/Sites/logs/mysite.com.error.log
    LogLevel warn
    CustomLog /Users/your_username/Sites/logs/mysite.com.access.log varnishcombined
 
    <Directory /Users/flynsarmy/htdocs/work/qpsmedia/qpsstats/>
      Options Indexes FollowSymLinks
      AllowOverride All
      Require all granted
    </Directory>
 
    SSLEngine on
    SSLCertificateFile    /Users/your_username/Sites/certs/server.crt
    SSLCertificateKeyFile /Users/your_username/Sites/certs/server.key
 
    <FilesMatch "\.(cgi|shtml|phtml|php)$">
      SSLOptions +StdEnvVars
    </FilesMatch>
  </VirtualHost>
</IfModule>

 

Finally, restart apache and you should be good to go!

1
sudo apachectl restart

 

Resources

Read More »

Posted in Database

If you want to back up your MySQL databases you’ll be familiar with

1
mysqldump -uroot -p --all-databases > dump.sql

However this includes the information_schema, mysql and performance_schema databases. These are often not only unwanted in the dump, but can potentially cause issues on import.

To exclude these databases from your dump use the following script courtesy of user RolandoMySQLDBA on the StackOverflow forums.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MYSQL_USER=rootMYSQL_PASS=rootpasswordMYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"
 
DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}
 
DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done
 
MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > all-dbs.sql

 

Read More »

Posted in Linux, Uncategorized

I have a bunch of sites in /var/www and need individual user logins with access to their respective sites. In this tutorial I’ll go over how to create a user, chroot jail them and allow access to specific folders (in our case web directories).

For reference I’m using a standard LAMP server on Ubuntu:

1
2
sudo apt-get install -y tasksel
sudo tasksel install lamp-server

but this tutorial will work for any web server configuration.

 

1. Create User, Assign Web Group

1
2
3
4
5
6
7
# Create the user setting group to www-data
sudo useradd -Ng www-data myuser
sudo passwd myuser
 
# Restrict login to SFTP only
sudo groupadd sftp-only
sudo usermod myuser -G sftp-only

 

Create their web directory and provide access

With the new user created, make a directory matching their website’s name and mount the real website folder to it:

1
2
3
4
5
6
7
8
9
# Create chroot directory and set permissions
mkdir -p /home/myuser/mysite.com/html
chmod 755 /home/myuser/mysite.com/html
 
# Mount the destination directory at the directory we just created
mount --bind /var/www/mysite.com/html /home/myuser/mysite.com/html
 
# Add the above command to /etc/rc.local to mount it on boot
nano /etc/rc.local

 

Restrict the user to SFTP Only

We only want to allow SFTP access for this user. First open /etc/passwd and make sure the end of the line has /bin/false like so:

1
2
tail -n1 /etc/passwd
# myuser:x:1001:33::/home/myuser:/bin/false

Now edit /etc/sshd/sshd_config to allow only SFTP myuser:

1
2
3
4
5
Match User myuser
  ChrootDirectory /home/myuser
  ForceCommand internal-sftp
  AllowTcpForwarding no
  X11Forwarding no

Restart the SSHD service:

1
sudo service sshd restart

Now when you try to SSH in with this user you’ll get the error:

This service allows sftp connections only.

 

That’s it! They should now be able to SFTP in and will only have a mysite.com directory with access to their web files.

 

Further Reading

mihai.ile’s post on Stack Overflow – How can I chroot sftp-only SSH users into their homes?

Read More »

Posted in Database

When doing a

1
mysqldump --all-databases -uUSER -p > dump.sql

I was getting the error

mysqldump: Got error: 1017: Can’t find file: ‘./dbname/tablename.frm’ (errno: 13 – Permission denied) when using LOCK TABLES

The error was caused by the table files in my MySQL data directory being owned by the wrong user so MySQL couldn’t properly read them. The solution is simple.

Find your MySQL Data Directory, Fix Permissions

1
2
3
4
5
6
7
8
9
10
11
12
13
$ mysql -uroot -p -e 'SHOW VARIABLES WHERE Variable_Name="datadir"'
 
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /usr/local/var/mysql/ |
+---------------+-----------------------+
 
# Check owner of each file
$ ls -lh /usr/local/var/mysql/
 
# Update to correct owner
$ sudo chown -R _mysql:admin /usr/local/var/mysql/*

After fixing the permissions on all files in this directory, I was able to mysqldump correctly.

Read More »

Posted (Updated ) in Javascript, PHP

Dropzone is a really nice lightweight javascript upload library with drag drop support and a high level of customisability. The documentation for direct uploads to Amazon S3 with a PHP backend are entirely lacking though so I’ve whipped up a complete tutorial on how to make it happen. For this tutorial I’ll be using Laravel but the script is very simple and any PHP backend will work.

 

What You’ll Need

  • AWS Access Key
  • AWS Secret Key
  • AWS Region
  • AWS Bucket Name

As part of Laravel I’m using dotenv to store these values and retrieving them with getenv() but they can be stored as config variables or any other method you like.

Read More »

Posted in Uncategorized

Occasionally after running a brew update && brew upgrade I’ll attempt to start apache with sudo apachectl start and get the error


[Thu Jan 25 08:53:02.769633 2018] [core:warn] [pid 41502] AH00111: Config variable ${APACHE_LOG_DIR} is not defined
[Thu Jan 25 08:53:02.769654 2018] [core:warn] [pid 41502] AH00111: Config variable ${APACHE_LOG_DIR} is not defined
AH00543: httpd: bad user name ${APACHE_RUN_USER}

I also notice OSX’s built in apache is running instead of homebrews. But where should the envvars file go?

 

The Fix

Firstly disable OSX’s built in apache:

1
sudo launchctl unload -w /System/Library/LaunchDaemons/org.apache.httpd.plist

Drop your envvars file in /usr/local/Cellar/httpd/2.4.*/bin folder replacing the * with your version number.

You should now be able to sudo apachectl start again.

Read More »

Posted in Uncategorized

For a while now I’ve been having an issue where when I plug my headphones into the headphone jack of my Logitech 5.1 PC speakers, some YouTube and Facebook videos wouldn’t have sound, but most would.

I found this Reddit thread on the topic however the best solution seemed to be:

Hopefully everyone has fixed this by now, however I fixed this by changing my Realtek settings from 7.1 to stereo. – VeryRedChris

I went poking around in my sound settings (Windows 10) and made the following adjustment: Sound – Playback Devices – Speakers (Realtek High Definition Audio) – Properties – Spatial Sound and set Spatial sound format to Windows Sonic for Headphones and made sure Turn on 7.1 virtual surround sound checkbox was checked. This fixed the issue! If you have Chrome open, restart it and any other existing audio apps for the changes to take affect.

Read More »

Posted in Linux

I’ve been trying to clone a private git repository from BitBucket and getting the response:

$ git clone git@bitbucket.org:my/repo.git
Cloning into ‘repo’…
Permission denied (publickey).
fatal: Could not read from remote repository.

There are two things that need to be done to fix this.

 

Add your SSH Key to BitBucket

Firstly, make sure your git server has your SSH key. I’m using BitBucket so as per their documentation:

1
2
ssh-keygen
cat ~/.ssh/id_rsa.pub

Copy and add your key to Settings – SSH Keys area in BitBucket.

 

Add your Key to the SSH Agent

If this still isn’t enough to fix the above error you may need to add your new key to your machines SSH agent.

1
2
3
4
5
# Make sure SSH agent is running
eval `ssh-agent -s`
 
# Add your key to the agent
ssh-add ~/.ssh/id_rsa

 

Give it a try now and you should be all good. Thanks to Srikanth Kondaparthy and user456814 for their helpful posts on Stack Overflow.

Read More »

Posted in Uncategorized

Our firewall at work restricts us to only port 80 and no access to SSH – which as you can imagine for a web developer is a pretty big issue. Below I’ll describe the various methods of routing around this crap.

 

Create a SOCKS5 Proxy with SSH

If your firewall restricts which sites you can visit but you have access through SSH to a remote server, route your browser and other traffic through that server with a SOCKS5 proxy. This is called Dynamic Port Forwarding:

1
ssh -f -N -D 1080 remote-server

The above command creates a SOCKS5 proxy server on port 1080 of your machine which sends all traffic through remote-server. 

Use it with you browser:

Now use the server in Firefox:

  • go to Edit -> Preferences -> Advanced -> Network -> Connection -> Settings…
  • check “Manual proxy configuration”
  • make sure “Use this proxy server for all protocols” is cleared
  • clear “HTTP Proxy”, “SSL Proxy”, “FTP Proxy”, and “Gopher Proxy” fields
  • enter “127.0.0.1” for “SOCKS Host”
  • enter “1080” (or whatever port you chose) for Port.

Use it with git:

You can also configure SSH git origins to work with your proxy:

Open ~/.ssh/config and add

1
2
3
Host bitbucket.org
    User git
    ProxyCommand nc -x localhost:1080 %h %p

Now you can just clone/push/pull as normal. See here for more information.

Read More »