Category Archives: GeneralSQL

Posts regarding SQL general topics.

What Is SQL Hacking/Injection?

Posted on by
Reply

“SQL injection or SQLi is a code injection technique that exploits a security vulnerability in some computer software. An injection occurs at the database level of an application (like queries). The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and unexpectedly executed. Using well designed query language interpreters can prevent SQL injections. In the wild, it has been noted that applications experience, on average, 71 SQLi attempts an hour. When under direct attack, some applications occasionally came under aggressive attacks and at their peak, were attacked 800-1300 times per hour.”

SOURCE : http://en.wikipedia.org/wiki/SQL_injection

—————————

One of the more friendly articles on SQL injection. Will be trying to find more in future..

A Quick SQL Database Notes | Keys

Posted on by
3

SQL Database Keys

Ho ho ho , another Sunday morning and I am feeling much better than yesterday. Been a bit busy and stressed out with work , school and a visit to the Dentist. I couldn’t come up with anything that is more than a few lines last night but after reading on Databases , for my degree course , I came up with something interesting and related to Databases.

I be posting on database keys. Thats right , keys. I am sure you remember we talked about two such keys when I posted on the sample database. If you don’t remember or you have not seen it before , one such tutorial is here.They are “Primary” and “Foreign” keys.

A primary key is an unique key which is used to locate a particular row in the database. For example , finding an employee’s information using his employee id. You might wonder , why don’t we use his name? Well there might be two people with the same name! Thats also true for the birthday , address and phone number. On the other hand , the employee id is an internal numbering which is internally controlled by the company. It only makes sense that it should be the only way we are going to be able to find the employee information , unaffected by the outside influence.

A foreign key , on the other hand , is a primary key of a table on the another table. If you looked at our sample database page , you will see that employee id is also present in the department table. Therefore , employee id is a primary key in one table and a foreign key in another.

So what other types of keys have we not talked about? Plenty it seems. Below , I will be putting up a summary of the keys in the database , for easy reference for you and me. I am sure I will be looking back to this article every now and then.

  • Primary Key – A unique key that is used to quickly locate records by the database
  • Foreign Key – A primary key on one table that is used as a constraint in another table
  • Compound Key or composite Key – A key that includes more than one field
  • Superkey – A set of columns for which no two rows can have the exact same value
  • Candidate Key – A minimal superkey
  • Unique Key – A superkey used to require uniqueness by the database
  • Alternate Key – A candidate key that is not the primary key
  • Secondary Key – A key used to look up records but that may not guarantee uniqueness

Keys Conclusion

Phew. Thats all for keys. Glad I got something to write on. Cya!

Whats Been Done

Posted on by
Reply

Lets see what have we looked at so far

- Basic SQL ( CRUD – Creat , Read , Update , Delete )
- Basic Programming ( Loops , If-Else , Functions … )
- Basic OOP ( Classes/Subclasses/Constructors … )

This about covers all the basic , I think. Maybe we should move into integration with SQL and Programming Languages. Probably LINQ and PHP/MySQL. After all that , we can talk about basic networking. Once all that is done , next would be security. Great!

SQL SELECT DISTINCT Statement

Posted on by
Reply

SELECT DISTINCT

SELECT DISTINCT In Excel

Here is something very close to your heart if you been using spreadsheet programs such as Microsoft Excel. You have rows and rows of customer orders to process and you want , or your manager wants , to know how many customers are currently in the list. For this , you can’t just count how many rows you have. Maybe some of your customers ordered twice or in different time then you might have a problem of double counting them. What you need to is to know how many of them are DINSTINCT.

Here is how you might have done it in Excel ,

Data -> Sort & Filter -> Advanced Filter -> Select unique items & copy to a new range

How do we do the same in SQL? Imagine we have rows and rows of employees , from it you want to know how many of the employees have the same title.

Before I show you the code , here is the syntax of it ,

SQL SELECT DISTINCT Syntax

SELECT DISTINCT column_name(s)
FROM table_name;

I think by now you should be able to come up with the code , if not here it is ,

SELECT DISTINCT emp_title
FROM employee;

Play with it and hope it helps!

SQL Delete Statement

Posted on by
Reply

I am feeling sleepy today so lets do some destruction to lift up my mood. Back to our database , say you want to delete a row. Maybe one of the employees in the company has left to another company so you need to close his account in the database. How do we do that? Here is how.

DELETE FROM table_name
WHERE column_name=some_value

So if you would like to remove a user called “John Evan” , 

DELETE FROM employee
WHERE emp_name='John Evan';

Then how do we undo it? Can’t! Too bad :P So you are going to have to add back John Evan if you followed my examples. OOPS!!!!!

Next , what about removing all the employees? Maybe you want to clear all out. 

DELETE FROM employee;

or

DELETE * FROM employee;

Without WHERE keyword , it will go to each row in the table and delete them one by one. Again . it cannot be undone. Of course the table , employee , itself will remain. You just removed the rows but not the table itself. So how do we delete the table itself and all the rows together?

Here is how.

DROP TABLE employee;

Taking to extreme , what if you want to delete the entire database? If you guessed “DROP DATABASE table_name” then you are right! Just substitute table with database from our previous example. 

DROP DATABASE database_name;

By now you should be able to CREATE a database and a table , define COLUMNS , INSERT data into the table , UPDATE them and DELETE them.

Updating Tables In SQL

Posted on by
Reply

Lets do a short tutorial on updating information in SQL tables using UPDATE keyword. The main reason is simple , everyone makes mistakes. There will definitely be typos of names and descriptions. Of course , even if there is not any mistakes initially , there is bound to be changes later down the road and it is important to know how to update the tables. Here is how it looks like , 

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

How do we apply this to our database? Lets say we want to change the name of the department from Finance to Accounting & Finance.

update department
set dept_name='Accounting & Finance'
where dept_id=101;

When you do the SELECT statement on the department table again , you will find that the word ‘Finance’ has been changed to ‘Accounting & Finance’. You can of course change it back and forth. Happy playing! :P

SELECT Statement with AND & OR Operators

Posted on by
4

Lets take a breather from JOIN statements we been looking at and lets look at AND and OR operators to be used in conjunction with with the SELECT statement to query information from the database.

Here is a sample SELECT statement using WHERE clause


select emp_name as 'Name', emp_sex as 'Sex', emp_salary as 'Salary' from employee where emp_sex = 'Male';

This will return you with 3 rows , excluding the lone female we have in the sample database.

Name Sex Salary
Michael Bay Male 90000.00
John Evan Male 5000.00
George Chan Male 50000.00

Its all fine but what if you want to select those who are BOTH Male AND has salary of more than 10000.00? Of course , this mean only two out of three will qualify. SO how do we do this in SQL?


select emp_name , emp_sex, emp_salary from employee where emp_sex = 'Male' and emp_salary > 5000;

Here we are looking for employees who are both Male and must have salary of more than 5000. The important thing to note is that using “AND’ means both the conditions must be satisfied. The query result for this statement is of just 2 rows.

Name Sex Salary
Michael Bay Male 90000.00
George Chan Male 50000.00

So what about OR? Try it. Replace ‘and’ with ‘or’ in the previous statement , like this ..


select emp_name , emp_sex, emp_salary from employee where emp_sex = 'Male' or emp_salary > 5000;

Guess what is the result from this query? If you say all three rows are returned as the first query then you are right. Because using ‘OR’ operator , only one of the conditions must be right. Either the employee is Male or has salary more than 5000. Since John Evan is Male , it qualifies him even though his salary is not more than 5000.

Hope this helps if you are wondering how is it possible to apply two conditions in SQL Select statement. See ya!