SQL Injection Cheat Sheet

http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/#AboutMySQLandPHP

Cron Job to Backup the Database(mysql) and send it as an email attachment

mysqldump mydatabase -ujerome -pmypassword > /home/jerome/database_dump.sql
#
#
zip -q /home/jerome/database_dump.zip  database_dump.sql
#
#
mutt -s "Email Subject" -a /home/jerome/database_dump.zip sjnlabs@gmail.com
#
#
rm -f /home/jerome/database_dump.zip /home/jerome/database_dump.sql


add the execute permission to the user

make sure the server forward mails to outside email
 or you may have to setup a forwarder

setup a cron job /home/jerome/backup_commands.sh

import large csv files intp mysql

you can edit the php.ini

or

i am using XAMPP 1.8.1 on Windows 7 Ultimate Edition

my xampp directory >>> D:\xampp
and database >>> sampledb

copy the data file inside D:\xampp\mysql\data\sampledb

CREATE TABLE IF NOT EXISTS `table2` (
  `FirstName` text,
  `LastName` text,
  `Company` text,
  `Address` text,
  `City` text,
  `County` text,
  `State` text,
  `ZIP` text,
  `Phone` text,
  `Fax` text,
  `Email` text,
  `Web` text
)

* this is not may not be the ideal way create table
   really wanted to create the table in with the loading of data and add the auto-incremental field

my data file is around 22MB and 349996 rows

LOAD DATA INFILE 'data.txt' INTO TABLE table2
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

mySQL mulitple table update

UPDATE config, tmp_config
SET
 config.new1 = tmp_config.new1,
 config.new2 = tmp_config.new2
WHERE tmp_config.tmp_id = config.id

UPDATE account, vtiger_accountshipads 
SET account.acc_shipping_address=vtiger_accountshipads.ship_street, account.acc_shipping_po_box=vtiger_accountshipads.ship_pobox, account.acc_shipping_city=vtiger_accountshipads.ship_city , account.acc_shipping_state=vtiger_accountshipads.ship_state, account.acc_shipping_postal_code=vtiger_accountshipads.ship_code , account.acc_shipping_country=vtiger_accountshipads.ship_country
WHERE vtiger_accountshipads.id=account.acc_id

SQL Fairy

http://sqlfairy.sourceforge.net/

SQL::Translator is a group of Perl modules that manipulate structured data definitions (mostly database schemas) in interesting ways, such as converting among different dialects of CREATE syntax (e.g., MySQL-to-Oracle), visualizations of schemas (pseudo-ER diagrams: GraphViz or GD), automatic code generation (using Class::DBI), converting non-RDBMS files to SQL schemas (xSV text files, Excel spreadsheets), serializing parsed schemas (via Storable, YAML and XML), creating documentation (HTML and POD), and more. New to version 0.03 is the ability to talk directly to a database through DBI to query for the structures of several databases.

Through the separation of the code into parsers and producers with an object model in between, it's possible to combine any parser with any producer, to plug in custom parsers or producers, or to manipulate the parsed data via the built-in object model. Presently only the definition parts of SQL are handled (CREATE, ALTER), not the manipulation of data (INSERT, UPDATE, DELETE).

PHP Year 2038 problem

What exactly is the Year 2038 problem?

"The year 2038 problem (also known as Unix Millennium Bug, Y2K38 by analogy to the Y2K problem) may cause some computer software to fail before or in the year 2038. The problem affects all software and systems that store system time as a signed 32-bit integer, and interpret this number as the number of seconds since 00:00:00 UTC on January 1, 1970."

Difference in Delete and Truncate in MySQL

hmm wondering what the difference is.... in Oracle
DELETE should follow COMMIT

TRUNCATE sorry no return bye bye command..

in MySQL...

lets take an table called
users
{
   id (INT: Primary Key | AUTO INCREMENT),
   name : VARCHAR(20)
}


id     name
1      Tania
2      Jane
3      Andrea

DELETE FROM users -----> deletes all records

so does

TRUNCATE TABLE users ----> but id is set to 1 again...

mysql simple update query 2 tables

UPDATE destination_table
SET my_column =

(  SELECT STR_TO_DATE( temp_column, '%d-%M-%y' )

  FROM `temp_table`

  WHERE `temp_table`.temp_id=destination_table.my_id  )
WHERE my_id >= 1928

destination_table (my_id, my_column)

temp_table(temp_id, temp_column )

MySQL Storage Engines

SHOW ENGINES\G

Engine	Support	Comments
MyISAM	DEFAULT	Default engine as of MySQL 3.23 with great performance
MEMORY	YES	Hash based, stored in memory, useful for temporary tables
InnoDB	YES	Supports transactions, row-level locking, and foreign keys
BerkeleyDB	NO	Supports transactions and page-level locking
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)
EXAMPLE	YES	Example storage engine
ARCHIVE	YES	Archive storage engine
CSV	YES	CSV storage engine
ndbcluster	NO	Clustered, fault-tolerant, memory-based tables
FEDERATED	YES	Federated MySQL storage engine
MRG_MYISAM	YES	Collection of identical MyISAM tables
ISAM	NO	Obsolete storage engine

How can I import a MySQL dumpfile into my database

Q. How can I import a MySQL dumpfile into my database? My old hosting provider gave me data.sql file. I do have access via ssh to server. I'm using CentOS Linux 5 server.

A. You can easily restore or import MySQL data with mysql command itself. First you need to login to your system using ssh or putty (from Windows based system). For example:

Type the following command at the shell prompt:

$ ssh loginname@server.hosting.com

Now type following command to import sql data file:
$ mysql -u username -p -h localhost data-base-name < data.sql

If you have dedicated database server, replace localhost name with actual server name or IP address:
$ mysql -u username -p -h 202.54.1.10 databasename < data.sql
OR use hostname such as mysql.hosting.com
$ mysql -u username -p -h mysql.hosting.com database-name < data.sql

If you do not know the database name or database name is included in sql dump you can try out something as follows:
$ mysql -u username -p -h 202.54.1.10 < data.sql

source: http://www.cyberciti.biz/faq/import-mysql-dumpfile-sql-datafile-into-my-database/

switch case mysql

select
CASE month 
when "01" then "January"
when "02" then "February"
when "03" then "March"
when "04" then "April"
when "05" then "May"
when "06" then "June"
when "07" then "July"
when "08" then "August"
when "09" then "September"
when "10" then "October"
when "11" then "November"
when "12" then "December"
END
from calendar where year = "2011" order by month