Showing posts with label Mysql. Show all posts
Showing posts with label Mysql. Show all posts
Monday, October 21, 2013
Preventing PHP websites from SQL injections
What is SQL injection:
SQL injection is a way of breaking the websites SQL query and customize it according to the hacker.
For example, if your website query for user to login is:
$u = "Ashwin";
$p = "TheCodePress";
mysql_query("SELECT * FROM users WHERE uname='$u' AND upass='$p' ");
But if i'm a hacker then i'll simply do something like this to break the security of your login system:
$u = "Ashwin' -- ";
$p = "Hacked";
mysql_query("SELECT * FROM users WHERE uname='$u' AND upass='$p' ");
So, what i have done ?
I have simply entered my correct username but after that i have added ' -- and this means comment in SQL.
So we have commented the rest of the portion of the query. That means we now don't need to enter password, we'll directly login into the website.
How to Solve this problem:
Well, the best way to do it without using any library or API is to use in built PHP functions. Such as mysql_real_escape_string(); and htmlentities();
If you are using mysql_real_escape_string function then all the vulnerable symbols will be parse, but it will parse safely.
PHP Code: So the code will be something like this:
$u = mysql_real_escape_string("Ashwin' -- ");
$p = mysql_real_escape_string("Hacked");
mysql_query("SELECT * FROM users WHERE uname='$u' AND upass='$p' ");
Is their any other better way to do it?
Yes, you can use the Library such as PDO or MYSQLI.
If you are more familiar with object oriented programming (OOP) then i'll prefer you to go with PDO.
Soon, i'll be too writing tutorials about PDO and Mysqli. You can learn it now on PHP manual.
Saturday, July 20, 2013
Basic pagination with Mysql, PHP
Have you ever noticed why blogger or wordpress or any other blogging platform have added a 'Next Page' button or you ever noticed that why facebook directly won't shows all the posts as well as twitter won't load the tweets ? The reason is: while loading tweets or loading status or articles from database developers loop over the database again and again and if their are many thousands or hundreds of status, tweets etc.. than the page get slow/crashes. That's the reason why they use pagination. By the way facebook and twitter loads their feed using Ajax.
After connecting let's move on adding tables and columns. First we'll create a table called 'page' and then we'll create column which name will be 'data' just for example i'm adding this names. You can even use PHPmyadmin (PMA) to make tables columns. Also i have added numbers from 1-20. Refer below image to understand it properly.
It just looks hard but it's not hard, just need to do the subtract and divide part properly and the it's done.
Let's first connect to our database:
PHP: Connecting to mysql database.
<?php
$db_host = 'localhost'; // your mysql host
$db_user = 'ashwin1999'; // your mysql user name
$db_pass = ''; // password not set
$db_name = 'pagination';
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
?>
$db_host = 'localhost'; // your mysql host
$db_user = 'ashwin1999'; // your mysql user name
$db_pass = ''; // password not set
$db_name = 'pagination';
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
?>
After connecting let's move on adding tables and columns. First we'll create a table called 'page' and then we'll create column which name will be 'data' just for example i'm adding this names. You can even use PHPmyadmin (PMA) to make tables columns. Also i have added numbers from 1-20. Refer below image to understand it properly.
Let's begin with PHP code blocks!
PHP: Pagination code
<?php
@$p = $_GET["page"]; //Getting Page number
$pages_query = mysql_query("SELECT COUNT(data) FROM page"); // Counting total rows
if($p=="" || $p=="0" || $p>$pages_query) { //checking is p is set and greater than 0
$p = 1; //if not set than setting it to 1
}
$per_page = 5; //Total data to display per page
$pages = ceil(mysql_result($pages_query, 0) / $per_page); //dividing total rows with total data to
display for example 20/10=2 so 2 pages
$start = ($p - 1) * $per_page; // subtracting $p value with 1 and multiplying it with $per_page for example 2-1=1*10 = 10
$query = mysql_query("SELECT data FROM page ORDER BY data ASC LIMIT $start, $per_page"); //Running our query
while($fetch_data = mysql_fetch_array($query)) { //fetching data using array method
echo $fetch_data["0"]."<br />"; //printing the data
}
for($a=1;$a<=$pages;$a++) { //using for to display number
echo "<a href='?page=$a' class='page_link'>$a</a> "; //printing numbers also using link tags
}
?>
It just looks hard but it's not hard, just need to do the subtract and divide part properly and the it's done.
Subscribe to:
Posts (Atom)
