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
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
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
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:
#!/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¶
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¶
mysqldump dbName | gzip -c | pv | ssh user@host 'zcat -d | mysql dbName'
Dump Remote DB, Gzip and Pipe to Local File¶
ssh user@host -- "mysqldump dbName | gzip -c" > /path/to/dbdump.sql.gz
Duplicate DB¶
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):
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
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:
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:
SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1;
105 OR 1=1 into the input field. Read more here.
See other related pages