MySQL is a specific type of SQL database.

It gained huge popularity thanks to being powerful, fully featured and also completely free open source software.

Hosting companies almost certainly offered MySQL databases and so it became the defacto database for LAMP software.

Most open source PHP packages are built around a MySQL database.

Learning MySQL

There are loads of online resources for learning MySQL, though this one is probably the best to start with

Working with MySQL

For a PHP developer, it is well worth getting phpMyAdmin or Adminer installed.

PHPStorm also offers a lot of MySQL functionality

Command Line MySQL

For working with MySQL on the command line, the first thing we should do is create a ~/.my.cnf file

This can have the correct MySQL username and password in there.

!!! Warning: Make sure you set permissions so only you can read the file

1
2
3
4
5
6
7
echo "

[client]
user=root
password=rootpass

" > ~/.my.cnf

Once this is in place then you can run the mysql* command line tool without having to continually put in your user and pass. This also improves security because it stops sensitive details leaking into the currently running process list and command history.

Binary Data and the CLI Tool

If the database that you are working with contains a lot of binary data, then using the Command Line can be a very frustrating experience, as the data will not be escaped by default.

Thankfully, you can add the following line to the bottom of your .my.cnf file and all binary columns will be converted to hex before being displayed

1
binary-as-hex = true

Dumping and Migrating Databases

As a developer it is quite common for us to need to copy and move databases around between servers and environments.

The command to dump a database to a text file is mysqldump

1
mysqldump --single-transaction -h [dbHost] -u [dbUser] -p [dbName] > {dump_name}.sql

Skipping Data for Some Tables

Often there will be certain tables for which you don't really need the data and can save a lot of time and bandwidth by not including the data.

We can do handle this as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
#!/bin/bash

dbName="database_name"

dumpName="${dbName}.sql"

#list of tables for which we need the data
dataTables="
orders
etc
etc
"

echo "Dumping full DB structure:"
mysqldump --verbose --no-data=true $dbName > $dumpName

echo "Now Dumping Data:"
mysqldump $dbName $dataTables --verbose >> $dumpName

Piping Dumps over SSH

If we are moving database dumps between servers then it can save a bit of time and effort and also disk space to pipe the dump directly over SSH

We'll use pv to show us progress, so make sure you yum -y install pv

Here are a few variations for this idea:

Dump Local DB, Gzip, Pipe through SSH (with progress) to File on Remote Server

1
mysqldump dbName | gzip -c | ssh user@host 'cat > /path/to/dbdump.sql.gz'

Dump Local DB, Gzip, Pipe through SSH (with progress), Extract straight into MySQL

1
mysqldump dbName | gzip -c | pv | ssh user@host 'zcat -d | mysql dbName'

Dump Remote DB, Gzip and Pipe to Local File

1
ssh user@host -- "mysqldump dbName | gzip -c" > /path/to/dbdump.sql.gz

Duplicate DB

1
2
3
shell> mysqldump db1 > dump.sql  
shell> mysqladmin create db2  
shell> mysql db2 < dump.sql 

SQL injection

SQL injection is a code injection technique that might destroy your database.
SQL injection is one of the most common web hacking techniques.
SQL injection is the placement of malicious code in SQL statements, via web page input.

SQL in Web Pages

SQL injection usually occurs when you ask a user for input, like their username/userid, and instead of a name/id, the user gives you an SQL statement that you will unknowingly run on your database.
Look at the following example which creates a SELECT statement by adding a variable (txtUserId) to a select string. The variable is fetched from user input (getRequestString):

1
2
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
The rest of this chapter describes the potential dangers of using user input in SQL statements.

SQL Injection Based on 1=1 is Always True

Look at the example above again. The original purpose of the code was to create an SQL statement to select a user, with a given user id.
If there is nothing to prevent a user from entering "wrong" input, the user can enter some "smart" input like this:

UserId: 105 OR 1=1

Then, the SQL statement will look like this:

1
SELECT * FROM Users WHERE UserId = 105 OR 1=1;

The SQL above is valid and will return ALL rows from the "Users" table, since OR 1=1 is always TRUE.
Does the example above look dangerous? What if the "Users" table contains names and passwords?
The SQL statement above is much the same as this:

1
SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1;
A hacker might get access to all the user names and passwords in a database, by simply inserting 105 OR 1=1 into the input field. Read more here.

See other related pages