- Joined
- Jun 8, 2007
- Messages
- 1,985
- Reaction score
- 490
The MySQLi extension is PHP's MySQL Improved extension.
It's pretty easy to pick up if you know the mysql extension.
The difference is, that there are no more tacky SQL injections. No more tacky escape functions. No more embedding user input directly inside a query, (Yuck!)
How does it work?
Simple, instead of traditional queries, we use prepared statements. Prepared statements, in a nut-shell, are queries separated from dynamic data, (rather then containing the data in each query). This eliminates MySQL injections.
Get it? If not, read on...
In a normal query, the user data is sent to mysql like this:
The above query contains a huge flaw. The MySQL Injection. The variable, $_GET['id'], is provided by user input. Let's say for example, the user puts '1' as the id. When the data gets sent to the MySQL Server, this is the query it gets:
* Notice the '1' gets placed directly in the query.
Now, imagine if the user puts, (instead of [1]), [1" OR rank="admin]
The MySQL server will get this query,
This process is called a MySQL Injection. It's when a malicious user puts some code in the query in order to (usually) do malicious things to your site and/or database. It could be considered an attack, in many scenarios. The above attack would grab the data for all of the administrators, or the user with the ID of 1. This outcome would differ from the usual flow of the script, and is a BIG security vulnerability.
Do you know what a SQL injection is now? If not, just consider it an attack on the database.
With the MySQLi extension, when done properly, there ARE NO chances that a SQL injection can pass through.
By done properly, I mean, so long as you don't put ANY dynamic data in your queries, but instead pass the data with prepared statements properly.
Please visit the above link, and put the 5 PHP files on your site in a directory. (you can name the folder 'test', or whatever you wish)
- Create a table named "test_db"
- Now install this database table:
Run this in a query, such as mysql_query(), or in PhpMyAdmin.
Note: Since there's no dynamic data, I don't suggest using mysqli for this query. Only use mysqli extension on queries that DO use embedded variables, or dynamic data.
Now then, you should have a working prototype of a register, login, and update engine running under the mysqli extension.
There are some comments, not much, so it might be a little hard to get used to. I'll go over the basic functions you need for a query.
Hope that helps!! Please check
It's pretty easy to pick up if you know the mysql extension.
The difference is, that there are no more tacky SQL injections. No more tacky escape functions. No more embedding user input directly inside a query, (Yuck!)
How does it work?
Simple, instead of traditional queries, we use prepared statements. Prepared statements, in a nut-shell, are queries separated from dynamic data, (rather then containing the data in each query). This eliminates MySQL injections.
Get it? If not, read on...
In a normal query, the user data is sent to mysql like this:
PHP:
mysql_query('SELECT * FROM `users` WHERE `id` = "'.$_GET['id'].'"') or die(mysql_error());The above query contains a huge flaw. The MySQL Injection. The variable, $_GET['id'], is provided by user input. Let's say for example, the user puts '1' as the id. When the data gets sent to the MySQL Server, this is the query it gets:
Code:
SELECT * FROM `users` WHERE `id` = "1"Now, imagine if the user puts, (instead of [1]), [1" OR rank="admin]
The MySQL server will get this query,
Code:
SELECT * FROM `users` WHERE `id` = "1" OR rank="admin"This process is called a MySQL Injection. It's when a malicious user puts some code in the query in order to (usually) do malicious things to your site and/or database. It could be considered an attack, in many scenarios. The above attack would grab the data for all of the administrators, or the user with the ID of 1. This outcome would differ from the usual flow of the script, and is a BIG security vulnerability.
Do you know what a SQL injection is now? If not, just consider it an attack on the database.
With the MySQLi extension, when done properly, there ARE NO chances that a SQL injection can pass through.
By done properly, I mean, so long as you don't put ANY dynamic data in your queries, but instead pass the data with prepared statements properly.
You must be registered to see links
Please visit the above link, and put the 5 PHP files on your site in a directory. (you can name the folder 'test', or whatever you wish)
- Create a table named "test_db"
- Now install this database table:
Code:
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user` varchar(55) NOT NULL,
`pass` text NOT NULL,
`email` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;Note: Since there's no dynamic data, I don't suggest using mysqli for this query. Only use mysqli extension on queries that DO use embedded variables, or dynamic data.
Now then, you should have a working prototype of a register, login, and update engine running under the mysqli extension.
There are some comments, not much, so it might be a little hard to get used to. I'll go over the basic functions you need for a query.
PHP:
//instantiate the class with the host, db user, db pass, and db name
$mysqli = new mysqli('localhost','user','pass','database_name');
//call the following in an if statement, it returns true on success
//This query selects 4 fields of data where the user and pass match up. Notice the question marks. Always put question marks where the dynamic data would go.
if($stmt = $mysqli->prepare('SELECT id,user,pass,email FROM users WHERE user=? AND pass=?'))
{
//We use bind_param() to add in the dynamic data separate from the query.
//the 'ss' means, 'string string' (first for user, second for pass; in the order- any order- we put them- depending on the order of the '?' in the query.)
//Bind user, md5(pass), and email as strings; no escaping needed! =D
$stmt->bind_param('ss',$_POST['user'],md5($_POST['pass']));
//execute the query.. Simple enough..
$stmt->execute();
//We use bind_result to get the results from the query.
//Notice I selected 'id, user, pass, email' in the query.
//the same order I select them, I load them in the result
//The order of things is VERY important with 'bind' functions.
$stmt->bind_result($id,$user,$pass,$email);
//Kind of like, while($row=mysql_fetch_assoc($query)), but the large parts of that are done already (bind_result) =D
while($stmt->fetch())
{
////Assign some session vars from the query result!
$_SESSION['id']=$id;
$_SESSION['user']=$user;
$_SESSION['pass']=$pass;
$_SESSION['email']=$email;
}
$stmt->close();
} else die($mysqli->error());Hope that helps!! Please check
You must be registered to see links
for more help!
Last edited:
