How to mount a Windows share from Ubuntu WSL

sudo mkdir /mnt/smb

sudo mount -t drvfs '\\servername\c$' /mnt/smb

 

 

Ubuntu customizations compendium

Do this when moving into a new Ubuntu machine on Windows Subsystem for Linux:

Brighten bash colors and brighten the bash prompt:

.bashrc

LS_COLORS='rs=0:di=1;35:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lz=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.war=01;31:*.ear=01;31:*.sar=01;31:*.rar=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.webm=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=00;36:*.au=00;36:*.flac=00;36:*.mid=00;36:*.midi=00;36:*.mka=00;36:*.mp3=00;36:*.mpc=00;36:*.ogg=00;36:*.ra=00;36:*.wav=00;36:*.axa=00;36:*.oga=00;36:*.spx=00;36:*.xspf=00;36:';
export LS_COLORS

PS1='\e[37;1m\u@\h \e[35m\w/ \e[0m\$ '

------------------------------------

 

Turn off the bells

.inputrc:

 set bell-style none

------------------------------------

 More bells, brighten colors in VIM, and fix double-characters in VIM

.vimrc

 set background=dark
set t_u7=
set belloff=all

------------------------------------

 

How to deduplicate and reorganize 1 TB of unorganized pictures

I went from a 1 TB unorganized collection of mostly pictures and movies to a 300 GB compendium of pictures and movies organized by year and month.  Here is how.  I am documenting my steps here because I don't want to figure it out again.  This might not work for you, but I was satisfied with the results.

*** Build initial Compendium and backups ***

Collect all images into one place. (I used an external 7200 RPM HDD I had laying around.)

Make backups in several locations (I went with several external 2 TB 7200 HDDs)
(These can be a little slow because they get stuffed in a drawer for years... the intent is to fall back on these if I make a mistake or if a device fails.)

Purchase external SSD drive (I went with 2 TB).  This will be my primary drive for this project and others in the future.  During this project, I want fast file operations.  Once the dedup and reorg is complete, I will back up my output to multiple external 7200 RPM drives and use the SSD as the primary browsing/editing device for my family photos.

Copy the compendium of images to the external SSD.

*** Remove junk files ***

First, remove temp "junk" files.

I used bash (Linux) to remove various temporary files that I knew I would no longer need:

Install Windows Subsystem for Linux
Go to the Windows store and install Ubuntu
Launch Ubuntu

cd /mnt/f/dedup

Experiment with the command below to find the files you want to delete:
find /mnt/f/dedup -iname '.DS_Store' -type f

Careful - Once you are satisfied with the target, you can add the delete switch:

find /mnt/f/dedup -iname '.DS_Store' -type f -delete

I wanted to get rid of many types of files.  You might not want to do the same.
These are the commands I ran; your mileage may vary:

find /mnt/f/dedup -type f -iname "*.THM" -delete
find /mnt/f/dedup -type f -iname "*.aae" -delete
find /mnt/f/dedup -type f -iname "*.db"  -delete
find /mnt/f/dedup -type f -iname "*.doc" -delete
find /mnt/f/dedup -type f -iname "*.docx" -delete
find /mnt/f/dedup -type f -iname "*.ini" -delete
find /mnt/f/dedup -type f -iname "*.mov_" -delete
find /mnt/f/dedup -type f -iname "*.pdf" -delete
find /mnt/f/dedup -type f -iname "*.psd"  -delete
find /mnt/f/dedup -type f -iname "*.temp" -delete
find /mnt/f/dedup -type f -iname "*.tmp" -delete
find /mnt/f/dedup -type f -iname "*.xmp" -delete
find /mnt/f/dedup -type f -iname ".DS_Store"  -delete
find /mnt/f/dedup -type f -iname "._*.avi" -size -5k -delete
find /mnt/f/dedup -type f -iname "._*.jpg" -size -5k -delete
find /mnt/f/dedup -type f -iname "._*.mov" -size -5k -delete
find /mnt/f/dedup -type f -iname "._.DS_Store"  -delete
find /mnt/f/dedup -type f -iname "._IMG_*.JPG" -size -5k -delete
find /mnt/f/dedup -type f -iname "._IMG_*.JPG" -size -5k -delete
find /mnt/f/dedup -type f -iname "._IMG_*.jpg" -size -5k -delete
find /mnt/f/dedup -type f -iname "._MVI_*.MOV" -size -5k -delete
find /mnt/f/dedup -type f -iname "._[0-9]*.jpg" -size -5k -delete
find /mnt/f/dedup -type f -iname "._MVI_*.AVI" -size -5k -delete
find /mnt/f/dedup -type f -iname "._MIV_*.MOV" -size -5k -delete
find /mnt/f/dedup -type f -iname "._[0-9][0-9]" -size -5k -delete
find /mnt/f/dedup -type f -iname "._IMG_[0-9]*.HEIC" -size -5k -delete
find /mnt/f/dedup -type f -iname "._IMG_[0-9]*.jpeg" -size -5k -delete
find /mnt/f/dedup -type f -iname "._[0-9][0-9][0-9][0-9]*-[0-9][0-9][0-9][0-9]*" -size -5k -delete
find /mnt/f/dedup -type f -iname "._IMG_*.CR2" -size -5k -delete




*** Deduplication ***

I use a Windows application called "Duplicate Cleaner Free"

Install and run it.

I use these settings:
Find files with: Same content
More duplicate options: Same file extension
Search filters: Included: *.*

Scan location:
Start with a small section of your total project to get a sense of how the software works.  In fact, I scan each section of my project individually to keep things at a reasonable scope.  After processing all sections, I then run a final dedup that includes all of them (not just individually) because there are likely duplicates between sections.

The software may identify files that you realize are more junk files.  If so, go back and run bash commands to get rid of them, and then run a new scan.  You may have to repeat this multiple times to truly get rid of your junk files.

Once Duplicate Cleaner finds and displays a list of duplicates, it expects you to mark which files you want to delete.  Use the "Selection Assistant" to mass mark the files:

I removed the duplicates based on:
Mark "All but one file in each group".

You may not like these options.  Experiment to find what works well for you.

Click "File Removal" to delete the duplicates.

On the dialog box that follows, I use the following options:
Skip problem files: uncheck
Remove empty folders: check
Delete to the Recycle Bin: uncheck
Via Windows Shell: check

When satisfied, click "Delete files"

If you are removing thousands of files, it may take a while to run.  Don't freak out if Windows says the app is not responding.  My system took several minutes to delete about 250 GB of 100,000 duplicates on a SSD drive attached via USB 3.  You may see a black screen appear, disappear, and reappear a few times.

Once you have processed each section and the entire project, your dedup is complete.  Make more backups because this was time consuming and you don't want to have to do it again.  I had to revert to this point due to mistakes more than I'd care to admit.  That's why we make backups.

*** Reorganization ***

Create a folder to contain the reorganized files:
md f:\output

Create a folder to contain images that don't have dates:
md f:\output\remnant


Need to update Ubuntu so we can install exiftool
sudo apt-get update
sudo apt-get upgrade

I rebooted out (bad) habit.

sudo apt-get update
sudo apt install libimage-exiftool-perl

Run one of the following commands from the directory you want problem files to be placed.  For me, that was /mnt/f/output/remnant
Note: Not sure about the above statement. Not sure it matters where the command is run from.  No files went into my "remnant" subdirectory.

Either:
exiftool -o . '-Directory<CreateDate' -d /mnt/f/output/%Y/%Y-%m%%-c -r '/mnt/f/reorg/pics/'
exiftool . '-Directory<CreateDate' -d /mnt/f/output/%Y/%Y-%m%%-c -r '/mnt/f/reorg/pics/'


The command above copies all the files in the /mnt/f/reorg subdirectory to one called /mnt/f/output
It creates a directory structure based not on the file stamp, but on the "create date" stored in the exif data of the image.

-o = Copy over (don't move).  If you leave -o out, the tool does a move instead of a file copy.

-d = destination directory (/mnt/f/output/%Y/%y-%m)
%Y = YYYY
%y = yy
%m = mm
%%-c = Increment count by 1 for files with duplicate filenames.  I don't quite understand this option, but I cobbled it together from random web links.

So a subdirectory for files created in  March of 2012 would look like this:  /mnt/f/output/2012/2012-03

-r = Recursive (go through all the subdirectories) of the source directory
In this case, the source directory is /mnt/f/reorg


*** Cleanup ***

I ran the exiftool command without the -o option, which meant files were moved, not copied.  The idea is that I want to pull all the files out of the source directory and into the reorganized repository.  But how do we track down the files that exiftool could not migrate?

The find command can find all files.  If you redirect output to a file, you will have a list of files to work through.

From the reorg directory, run this command:

find . -type f > remaining_files.txt

-type f = Look for "files" (as opposed to directories)

I still had a bunch of junk files remaining that I had to delete.


*** Collection of scripts used during cleanup that I should explain later but we both know I will forget to do so ***
 

 This command below will find and remove all files starting with "._" (without quotes).  That's pretty extreme, so buyer beware.

find /mnt/f/reorg -type f -iname "._*" -delete

This next command will find and remove all empty directories to make the remaining job easier:
find /mnt/f/reorg -type d -empty -delete

-type d = Limit the find command to directories
-empty = Empty ones


find /mnt/f/output/2006 -type d -iname "*-1" -exec mv {} /mnt/f/output/2006/more/ \;

find . -type f
This would find all files remaining in the dedup directory that were not moved to the reorg directory.


find . -type f ! -iname "IMG_*.jpg" -and ! -iname "MVI*.AVI" -and ! -iname "dscn*.jpg" -and ! -iname "IMG*.MOV" -and ! -iname "DSCN*.MOV" -and ! -iname "kimg_*.jpg" -and ! -iname "xIMG_*.JPG" -and ! -iname "1 IMG_*.JPG" -and ! -iname "MVI*.MOV" -and ! -iname "DSC*.JPG" -and ! -iname "DSCF*.AVI" -and ! -iname "IMG_*.jpeg" -and ! -iname "._IMG_[0-9]*.jpeg" -and ! -iname "IMG_*.CR2" -and ! -iname "IMG*.HEIC"


rename_files_in_single_directory:
#!/usr/bin/bash
# Renames files from whatever.ext to whatever_001.ext
for file in $1/*.*; do ext="${file##*.}"; filename="${file%.*}"; mv "$file" "${filename}_001.${ext}"; done
#for file in $1/*.*; do ext="${file##*.}"; filename="${file%.*}"; echo "${filename}_001.${ext}"; done

cycle_through:
#!/usr/bin/bash
# Requires a list of directories in file /mnt/f/reorg/dirs.txt
while read dirname; do
        echo "Processing $dirname"
        rename_files_in_single_directory "$dirname"
done </mnt/f/reorg/dirs.txt

Command to find all directories in format YYYY-MM-1
(This was needed to find directories with duplicate filenames in a given month of a given year.)
find . -type d -iname "[0-9][0-9][0-9][0-9]-[0-9][0-9]-1" > dirs.txt

Add a custom directory to the path in bash

 Edit ~/.bashrc

Add the following line:

export PATH="$PATH:/<target_directory>"

 

Brighten ls output in bash

 I can't easily see the folder names returned by the 'ls' command in bash when running Ubuntu on Windows.  Here is the fix.

Edit ~/.bashrc

Append this line:

LS_COLORS="ow=01;36;40" && export LS_COLORS

Update 2023-07-15:

Not working as well as it did.  Here's a newer way:

LS_COLORS='rs=0:di=1;35:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lz=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.war=01;31:*.ear=01;31:*.sar=01;31:*.rar=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.webm=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=00;36:*.au=00;36:*.flac=00;36:*.mid=00;36:*.midi=00;36:*.mka=00;36:*.mp3=00;36:*.mpc=00;36:*.ogg=00;36:*.ra=00;36:*.wav=00;36:*.axa=00;36:*.oga=00;36:*.spx=00;36:*.xspf=00;36:';

export LS_COLORS

 

Customize and brighten the fonts in the bash prompt in Ubuntu on Windows

I run Ubuntu on Windows.  The prompt is too dark for me to see.  Here is how to brighten the prompt.

Edit ~/.bashrc

Append the following line:

 

PS1='\e[37;1m\u@\h \e[35m\w/ \e[0m\$ '

 

\u = Username

\h = Hostname

\w = Working directory

 

So \u@\h: makes the prompt
 

<username>@<hostname>:

 \e[ = Start a color scheme

The part that follows is a color:  37;1m

The 37 is the color.

The 1 says to use a bright version.

m somehow designates the end of the color sequence (but I'm not exactly sure of that part).

I made some more changes to make my life easier.  I added some spaces to the prompt before and after the working directory so I could double-click the working directory path and get the path easily into my copy buffer.

How to turn off the bell in bash

The bell is terribly loud in Ubuntu on Windows.  Here is how to disable it:

Edit ~/.inputrc

Contents:

 

set bell-style none

 

LAMP - Part 6 - Embed the form in a web page

My notes on how to create a LAMP form using PDO and MySQL:

Part 6: Embed the form

This is part 5 of a series:

Part 1 - Prepare mysql
Part 2 - Create the mysql login files
Part 3 - Retrieve all records
Part 4 - Insert a new record
Part 5 - Search for a record
Part 6 - Embed the form


Embed the form in your web site:

Insert this HTML into your web page:

<iframe src="http://www.whatever.com/form1.php" height="400" width="600">

LAMP - Part 5 - Search for a record

My notes on how to create a LAMP form using PDO and MySQL:

Part 5: Search for a record

This is part 5 of a series:

Part 1 - Prepare mysql
Part 2 - Create the mysql login files
Part 3 - Retrieve all records
Part 4 - Insert a new record
Part 5 - Search for a record
Part 6 - Embed the form



Create a PHP form to search for a set of records:

Contents of lookup.php:


<?php // lookup.php

echo <<<_END

<html>
<head>
<title>Lookup Test</title>
</head>
<body>
<form method="post" action="lookup.php">
Last name: <input type="text" name="lastName"> <br>
<br>
<input type="submit" value="submit">
</form>
<hr>
<br>

_END;

// Set variable $lastName if it was provided via a POST method
if (isset($_POST['lastName']) && (!empty($_POST['lastName'])))
{

// This is the code that sanitizes the user's input string
//$lastName = filter_var($_POST['lastName'], FILTER_SANITIZE_STRING);

$lastName = filter_var($_POST['lastName'], FILTER_SANITIZE_STRING, FILTER_FLAG_STR
IP_HIGH | FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_BACKTICK | FILTER_FLAG_ENCODE_AMP );

}
if (!empty($lastName))
{
echo "Entered Name: $lastName<br>";
echo '<br>';

// Retrieve records from mysql database

// Retrieve database connection info
require_once '/var/forms/login_reader.php';

// Build data source name and options
$dsn = "mysql:dbname=$db;host=$host;charset=$charset";
$opt = [
PDO::ATTR_ERRMODE=> PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE    => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES      => false,
];


// Set $targetLastName to value provided

// This appends a wildcard to whatever the user provided so it brings back a set of results

$targetLastName = $lastName . "%";

// Now try to connect to the database and retrieve records
try {

// We use PDO instead of mysqli so we can switch from mysql to MS SQL if needed

$pdo = new PDO($dsn, $user, $pass, $opt);

// Prepare the SQL statement to protect against SQL injection attacks.
// Notice the use of the question mark (?) placeholder.

$statement = $pdo-> prepare( "
       
                        select Firstname, Lastname
                        from Form1
                        where Lastname LIKE ?
                        order by Firstname asc
                        LIMIT 20
       
                " );

// Now that the SQL statement is prepared, execute it
// Provide the $targetLastName parameter

$statement->execute([$targetLastName]);

// If we found matching records, prepare to display them       

if ($statement->rowCount() > 0)
{
echo "Matching records found:<br><br>";
// Display each matching record
foreach ($statement as $row)
{
echo $row["Firstname"] . " " . $row["Lastname"];
echo "<br>";
}
}

// What do we do if no matching records are found?
else

{
echo 'No matching records found.<br>';
}


}

// Deal with problems with the database
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}

}

echo '<br>';
echo '<br>';
echo 'Click <a href="index.html">here</a> to return to Main Menu';
echo '<br>';

echo '</html>';
?>

LAMP - Part 4 - Insert a new record

My notes on how to create a LAMP form using PDO and MySQL:

Part 4: Retrieve all records

This is part 4 of a series:

Part 1 - Prepare mysql
Part 2 - Create the mysql login files
Part 3 - Retrieve all records
Part 4 - Insert a new record
Part 5 - Search for a record
Part 6 - Embed the form



Create a PHP form to insert a new record:

Contents of form1.php:

<?php // form1.php

echo <<<_END

<html>
<head>
<title>Form1 Test</title>
</head>
<body>
<form method="post" action="form1.php">
Values must be entered for BOTH fields.<br><br>
First name: <input type="text" name="firstName"> <br><br>
Last name: <input type="text" name="lastName"> <br>
<br>
<input type="submit" value="submit">
</form>
<hr>
<br>

_END;

// Set variable $lastName if it was provided via a POST method
if (isset($_POST['lastName']) && (!empty($_POST['lastName'])))
{

// This is the code that sanitizes the user's input string
//$lastName = filter_var($_POST['lastName'], FILTER_SANITIZE_STRING);

$lastName = filter_var($_POST['lastName'], FILTER_SANITIZE_STRING, FILTER_FLAG_STR
IP_HIGH | FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_BACKTICK | FILTER_FLAG_ENCODE_AMP );

}

// Set variable $firstName if it was provided via a POST method
if (isset($_POST['firstName']) && (!empty($_POST['firstName'])))
{

// Sanitize the user's input string

$firstName = filter_var($_POST['firstName'], FILTER_SANITIZE_STRING, FILTER_FLAG_S
TRIP_HIGH | FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_BACKTICK | FILTER_FLAG_ENCODE_AMP );

}

if (!empty($lastName) && !empty($firstName)  )
{
echo "Entered First Name: $firstName<br>";
echo "Entered Last Name: $lastName<br>";
echo '<br>';

// Insert the values into the mysql database
// To Do:
// - Check for existing entry.  Reject attempt if match exists.

// Retrieve database connection info
require_once '/var/forms/login_writer.php';

// Build data source name and options
$dsn = "mysql:dbname=$db;host=$host;charset=$charset";
$opt = [
PDO::ATTR_ERRMODE=> PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE    => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES      => false,
];

try {
// Build a PDO object

$pdo = new PDO($dsn, $user, $pass, $opt);

// Prepare the SQL statement to protect against SQL injection attacks.

$statement = $pdo-> prepare( "
       
                        INSERT INTO Form1
                        VALUES (DEFAULT, ?, ?)
       
                " );

// Now that the SQL statement is prepared, execute it
// Provide the parameters

$statement->execute([$lastName, $firstName]);
 
echo "Added...";

}


// Deal with problems with the database
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}

}

echo '<br>';
echo '<br>';
echo '<br>';
echo'Click <a href="index.html">here</a> to return to Main Menu';
echo '<br>';
echo '</html>';
?>

LAMP - Part 3 - Retrieve all records

My notes on how to create a LAMP form using PDO and MySQL:

Part 3: Retrieve all records

This is part 3 of a series:

Part 1 - Prepare mysql
Part 2 - Create the mysql login files
Part 3 - Retrieve all records
Part 4 - Insert a new record
Part 5 - Search for a record
Part 6 - Embed the form



Create an index file:

Create an index.html file for convenience.  Contents:

<html>
<head>
<title>Main Menu</title>
</head>
<body>
<br><br>
<a href="display_all.php">Display all</a> records</a>
<br><br>
<a href="lookup.php">Look up</a> a record</a>
<br><br>
<a href="form1.php">Insert</a> a record</a>
<br><br>
</body>
</html>


Create a PHP file to display all records:

Contents of display_all.php:

<?php // display_all.php

echo <<<_END

<html>
<head>
<title>Display_All Test</title>
</head>
<body>
<br>

_END;

echo '<br>';

// Retrieve database connection info
require_once '/var/forms/login_reader.php';

// Build data source name and options
$dsn = "mysql:dbname=$db;host=$host;charset=$charset";
$opt = [
PDO::ATTR_ERRMODE=> PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE    => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES      => false,
];


// Now try to connect to the database and retrieve records

try {

// We use PDO instead of mysqli so we can switch from mysql to MS SQL if needed

$pdo = new PDO($dsn, $user, $pass, $opt);

// Prepare the SQL statement to protect against SQL injection attacks.

$statement = $pdo-> prepare( "

                select Lastname, Firstname
                from Form1
                order by Lastname asc, Firstname asc
                LIMIT 20

        " );

// Now that the SQL statement is prepared, execute it

$statement->execute();

// If we found matching records, prepare to display them       

if ($statement->rowCount() > 0)
{
// Display each record
foreach ($statement as $row)
{
echo $row["Lastname"];
echo ", ";
echo $row["Firstname"];
echo "<br>";
}
echo "<br>";
echo "<hr>";
}

// What do we do if no matching records are found?
else

{
echo 'No records found.<br>';
}


}

// Deal with problems with the database
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}



echo '<br>';
echo '<br>';
echo 'Click <a href="index.html">here</a> to return to Main Menu';
echo '<br>';

echo '</html>';
?>

LAMP - Part 2 - Create the mysql login files

My notes on how to create a LAMP form using PDO and MySQL:

Part 2: Create the mysql login files

This is part 2 of a series:

Part 1 - Prepare mysql
Part 2 - Create the mysql login files
Part 3 - Retrieve all records
Part 4 - Insert a new record
Part 5 - Search for a record
Part 6 - Embed the form


Identify a directory outside of web root

You need to store your login credentials outside the web server directory.
This is to prevent the files from being downloaded if you accidentally misconfigure your web server.
In this example, I will store the credentials in
/var/forms


Create the files

cd /var/forms
touch login_reader.php
touch login_writer.php


Determine who apache runs as

ps aux | egrep "apache|httpd"

Since I run Ubuntu, apache runs as www-data




Change ownership of the files

By default the files will be owned by root and only root will be able to read/write the files.
You can see this by running the command:

ls -l

My files show:
owner = root
group = root


We need to change ownership so we can later assign permissions to the owner.
Recall that apache runs as www-data on Ubuntu, so we will change permissions to www-data:

chown www-data login_reader.php
chown www-data login_writer.php

ls -l


Change permissions on the files

My current file permissions:
owner: rw
group: r
world: r

Remove read permissions for world:

chmod o-r login_reader.php
chmod o-r login_writer.php

Grant write permissions for group (root):

chmod g+w login_reader.php
chmod g+w login_writer.php

Remove write permissions for www-data:
chmod u-w login_reader.php
chmod u-w login_writer.php

Finished permissions:
-r--rw---- 1 www-data root 0 Jun 21 18:53 login_reader.php
-r--rw---- 1 www-data root 0 Jun 21 18:53 login_writer.php


Determine character set:

You will need this to create your login files (see below).

Run this command against mysql:

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_name = "Form1";

My system comes back with latin1 


Populate login_reader.php:

<?php 
$host = 'localhost';
$db = 'myforms';
$user = 'testreader';
$pass = 'myStrongPassword';
$charset = 'latin1';
?>

Populate login_writer.php:

<?php 
$host = 'localhost';
$db = 'myforms';
$user = 'testwriter';
$pass = 'myOtherStrongPassword';
$charset = 'latin1';
?>

LAMP - Part 1 - Prepare MySQL

My notes on how to create a LAMP form using PDO and MySQL:

Part 1: Prepare mysql

This is part 1 of a series:

Part 1 - Prepare mysql
Part 2 - Create the mysql login files
Part 3 - Retrieve all records
Part 4 - Insert a new record
Part 5 - Search for a record
Part 6 - Embed the form


Create the database:

mysql -u root -p
create database myforms;
show databases;
use myforms;


Create the table:

This will create a table "Form1" with three fields: ID, LastName, and FirstName:

CREATE TABLE Form1 (
ID INT NOT NULL AUTO_INCREMENT,
LastName VARCHAR(255) NOT NULL,
FirstName VARCHAR(255) NOT NULL,
PRIMARY KEY (ID)
);



Populate the table with sample data:

INSERT INTO Form1 VALUES (1, 'Flintstone', 'Fred');
INSERT INTO Form1 VALUES (DEFAULT, 'Flintstone', 'Wilma');
INSERT INTO Form1 VALUES (DEFAULT, 'Rubble', 'Barney');
INSERT INTO Form1 VALUES (DEFAULT, 'Rubble', 'Betty');

SELECT * FROM Form1;



Create users:

We will be creating two users -- one for read operations and one for write operations.  (This is for demo purposes.  In my production application, I don't have the need for users to read from the database.  For tinkering purposes, I will document it here.)

CREATE USER 'testreader'@'%' IDENTIFIED BY 'myStrongPassword';
CREATE USER 'testwriter'@'%' IDENTIFIED BY 'myOtherStrongPassword';

SELECT User FROM mysql.user;



Grant privileges to the users:

GRANT INSERT ON myforms.* TO 'testwriter'@'%';
GRANT SELECT ON myforms.* TO 'testreader'@'%';


Test user access:

mysql -u testwriter -p
use myforms;
show tables;
INSERT INTO Form1 VALUES (DEFAULT, 'Flintstone', 'Pebbles');
SELECT * FROM Form1;

(The SELECT statement will be denied for the testwriter account)

mysql -u testreader -p
use myforms;
show tables;
INSERT INTO Form1 VALUES (DEFAULT, 'Rubble', 'Bambam');
SELECT * FROM Form1;

(The INSERT statement will be denied for the testreader account)

Install Apache and PHP on Ubuntu

[Summarized from Digital Ocean]

Install Apache:

sudo apt-get update
sudo apt-get install apache2
sudo apache2ctl configtest
sudo systemctl restart apache2
sudo ufw status
sudo ufw app list
sudo ufw app info "Apache Full"
sudo ufw allow in "OpenSSH"
sudo ufw allow in "Apache Full"
sudo ufw allow in "Apache"
sudo ufw allow in "Apache Secure"
sudo ufw enable

This will tell you the instance's public IP address so you can test the site:
curl http://icanhazip.com

Install PHP:

sudo apt-get install php libapache2-mod-php php-mcrypt php-mysql

Make PHP handling the default:

sudo vi /etc/apache2/mods-enabled/dir.conf

Change this line:

DirectoryIndex index.html index.cgi index.pl index.php index.xhtml index.htm

To be like this:

DirectoryIndex index.php index.html index.cgi index.pl  index.xhtml index.htm

[We simply moved index.php from the fourth option to the first.]

Restart Apache:

sudo systemctl restart apache2

Check Apache status:

sudo systemctl status apache2

Install command-line interpreter for PHP for easier debugging:

apt-cache show php-cli

sudo apt-get install php-cli

Might as well add perl DBI and CGI modules:

sudo apt-get install libdbi-perl libdbd-mysql-perl libcgi-pm-perl

MySQL on Ubuntu on AWS Lightsail

Some quick documentation on how I created an AWS Lightsail Ubuntu instance:

First create the instance:

Create instance --> Linux/Unix --> OS Only --> Ubuntu 16.04 LTS

Wait a few minutes, then open a shell prompt.

sudo apt-get update
sudo apt-get upgrade
sudo reboot
sudo apt-get update
sudo apt-get upgrade
sudo apt autoremove
sudo apt-get dist-upgrade
sudo reboot
sudo apt-get update
sudo apt-get upgrade

Nicely patched now.
To install mysql:

sudo apt-get install mysql-server
mysql_secure_installation

Now to test it:

mysql -u root -p
show databases;

How to update Ubuntu

sudo apt-get update
sudo apt-get upgrade

and sometimes

sudo apt-get dist-upgrade

How to install Ruby 2.0.0p247 and Rails 4.0 on Amazon Ubuntu instance

How I installed Rails

First install Ruby:

I tried to avoid using sudo.  I don't remember exactly when I used sudo in the steps below.  When you see "sudo", that's a post-install guess that I probably used it there.

sudo apt-get -y update
sudo apt-get -y install build-essential zlib1g-dev libssl-dev libreadline6-dev libyaml-dev
cd /tmp
wget http://cache.ruby-lang.org/pub/ruby/2.0/ruby-2.0.0-p247.tar.gz
tar -xvzf ruby-2.0.0-p247.tar.gz
cd ruby-2.0.0-p247/
./configure --prefix=/usr/local
make
sudo make install

----------------------

I installed Rails by following this web page:

https://www.digitalocean.com/community/articles/how-to-install-ruby-on-rails-on-ubuntu-12-04-lts-precise-pangolin-with-rvm

Noting the relevant bits here in case that page goes down/changes:

sudo apt-get update
sudo apt-get upgrades
sudo apt-get install curl

This installs RVM.  RVM is a Ruby version manager.  I want this in case I need to use different versions of Ruby (maybe later).
\curl -L https://get.rvm.io | bash -s stable
source ~/.rvm/scripts/rvm
rvm requirements

I don't know if this next part might be redundant.  I wonder if I should have installed RVM first, before installing Ruby.

rvm install ruby
rvm use ruby --default

This installs rubygems.  Not sure what gems are.  Wikipdedia says:

RubyGems is a package manager for the Ruby programming language that provides a standard format for distributing Ruby programs and libraries (in a self-contained format called a "gem"), a tool designed to easily manage the installation of gems, and a server for distributing them.

rvm rubygems current

This installs rails:
gem install rails



----------------------

Currently trying to follow this introductory Rails guide:

http://guides.rubyonrails.org/getting_started.html

The guide assumes I have SQLite3 installed.  I think this installed sqlite3:

sudo apt-get install sqlite3 libsqlite3-dev
sudo gem install sqlite3-ruby

I don't recall if I had to configure stuff for sqlite3.  There may have been on-screen instructions I had to follow.