Why one should use parameterized statement in PHP Hi All, Today I am going to explain why one should use parameterized statements while accessing the database and performing database operations. Parameterized statements basically have placeholders in the SQL query you use in the code. For example Simple SQL query: Select *from users where username=’”.$username.”’ and password=’”.$password.”’”; Parameterized Query would be “Select *from users where username=? and password=?” Here you can see that we have used ? rather than placing the user supplied data in the query. The number of place holders(?) in the query is equal to the number of variables in your SQL query. The advantages of using parameterized version are:•
Parameterized Queries are faster than normal queries.
•
These are more secure and prevent SQL injection. You can be sure of your application is safe from SQL Injection if you use parameterized queries.
•
One should be free from using these ‘(Single Quotes) which usually causes syntax errors in your
•
program.
To use these query use will not use simple mysql_connect(), instead of it you have to use Mysqli extension. So you will create a connection using mysqli_connect() to connect to your database and use these parameterized statements. Let me explain the whole concept with a simple example:Here is the complete code to authenticate user against a database in your PHP program.
Suppose you have stores username and password provided by the users in $username and $password variable. Here are the steps:•
Make a database connection $connect=mysqli_connect(<HOST_NAME,<DB_USER>,<DB_PASSWORD>,<DATABASE_N AME>)
•
Write the SQL query as :$SQL=”select userid from <TABLE_NAME> where username=? And password=?”;
•
Make a statement object using prepare() method as :$stmt=$connect->prepare($SQL);
•
Bind parameters to your SQL query using bind_param() method as :$stmt->bind_param("ss",$username,$password);
•
Execute the SQL query using execute() method as :$stmt->execute();
•
Bind the result to variables using bind_result() method as :$stmt->bind_result($userid);
•
Fetch the data from the database using fetch() method as :$stmt->fetch();
•
Close the database connection using close() method as :$connect->close();
Now your $userid variable will have the userid of the user if the username and password supplied by the user is valid else it will be empty. Now you can check against this variable and redirect the user to respective login page or authenticated page.
For More Information about PHP programmer & PHP programmers India visit http://www.phpprogrammers.com/