Tuesday, October 29, 2013

SSH Tunnel All Network Traffic - Windows 7

Problem:
While working on a some development, there became an issue with the ISP and they hijacked all HTTP requests to return a notice explaining that an issue needed to be resolved. I determined that HTTPS still worked as it uses SSL and therefore would be difficult (and possibly illegal) for them to hijack those requests.

Solution:
SSH is an encrypted connection and therefore was still available to use. Using PuTTY, I set up a connection as a tunnel. Once this was done, I could configure my browsers to use the local socks5 connection to one of our development boxes (make sure you trust your proxy end point as all non-secured traffic will be visible).

Setup a New connection

Enabled compression

Add a local port (dynamic) that will be used for forwarding

Once the port has been added

Note: These are the minimal settings and can be configured further depending on your needs.

This worked (with obvious increased latency due to another node between end points) and allowed me to get access to all HTTP web sites. However, I wanted to take this further for applications like Spotify without configuring each one independently. After some looking around as programs like Widecap, I determined that it would probably be something natively supported by Windows. Here is how I went about setting that.

Click the Internet Icon in the lower right

Click the Network Sharing Center Link

Click on Internet Options Link

Click the Connections tab and then click the LAN settings button

Check "Use a proxy server..." and then click the "Advanced" button

If you are doing 1 socks5 proxy like I am, clear all other addresses and add the local address and forwarded port from earlier.
There are a lot of additional settings, but this should work.

I strongly discourage trying to use this to circumvent employers from knowing of your activities, or for using this to try and remain anonymous online.

Wednesday, October 2, 2013

Laravel 4: Seeding Large CSV Files Using MySQL Load Data

Problem:
I wanted to quickly populate some tables using some large CSVs (400k+ records) but parsing the files and importing each line was out of the question and doing one mass insert would have used up a lot of memory. The only good solution is using MySQL's LOAD DATA functionality which can import the data within seconds. But figuring out how to do this with Laravel was not easy.

Solution:
When trying to execute the query using DB::statement() I kept getting met with an exception from MySQL saying that I had unbuffered queries active. This displayed with a suggestion of using a PDOStatement. After some searching the internet and trying some things, I found a working solution.

$csv = app_path().DIRECTORY_SEPARATOR."database".DIRECTORY_SEPARATOR."resources".DIRECTORY_SEPARATOR."locations.csv";

$query = sprintf("LOAD DATA INFILE '%s' INTO TABLE locations FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\\n' IGNORE 0 LINES (`location_id`, `country`, `region`, `city`, `postal_code`, `lat`, `lng`, `metro_code`, `area_code`)", addslashes($csv));

DB::connection()->getpdo()->exec($query);