MySQL injection protection

[undertaker]

How can you protect mysql injection? (from inserting different statements into the input field)

 

Thanks

[Maq]

How can you protect mysql injection? (from inserting different statements into the input field)

 

Thanks

Wow, I copied your question into Google and got thousands of relevant results, imagine that.

[Philip]

Wow, I copied your question into Google and got thousands of relevant results, imagine that.

What is this magical "Google" you speak of?!

[Maq]

Wow, I copied your question into Google and got thousands of relevant results, imagine that.

What is this magical "Google" you speak of?!

http://tinyurl.com/6oh56d

[cssfreakie]

Wow, I copied your question into Google and got thousands of relevant results, imagine that.

are you serious? i got this:

 

maybe i made a typo.

 

 

 

*sorry for trolling this just asked for it 

 

[jcbones]

Ah, you found Maq's pic!

 

[dadamssg87]

i'm redditor...was definitely about to upvote all your comments haha

[Maq]

Ah, you found Maq's pic!

 

cssfreakie....  how many times do I have to tell you, the pics I send you are strictly for personal pleasure.  That's it!

[dougjohnson]

The best way to protect against MySQL injections in php is to use "Prepared" statements.  You don't need to validate the user input since it is completely separated from the mysql statement.

 

Example:

 

$connection = new mysqli('server', 'username', 'password', 'database');
$result = $connection->prepare("SELECT products, usertype, special_pricing_user, special_pricing, pcconly FROM users WHERE username = ?");
$result->bind_param("s", $username);
$result->execute();
$result->bind_result($userproducts, $usertype, $special_pricing_user, $special_pricing, $pcconly);
while ($row = $result->fetch()) {
//
}

 

[cssfreakie]

another way could be to use mysql_real_escape_string or the newer mysqli_real_escape_string to sanitize the values before entering.

 

although i am more a fan of prepared statements

[Bl4ckMaj1k]

The best way to protect against MySQL injections in php is to use "Prepared" statements.  You don't need to validate the user input since it is completely separated from the mysql statement.

 

Example:

 

$connection = new mysqli('server', 'username', 'password', 'database');
$result = $connection->prepare("SELECT products, usertype, special_pricing_user, special_pricing, pcconly FROM users WHERE username = ?");
$result->bind_param("s", $username);
$result->execute();
$result->bind_result($userproducts, $usertype, $special_pricing_user, $special_pricing, $pcconly);
while ($row = $result->fetch()) {
//
}

 

 

For us Noobs, can someone please break down what's going on in this form?? (in Lamens terms?)

[blew]

The best way to protect against MySQL injections in php is to use "Prepared" statements.  You don't need to validate the user input since it is completely separated from the mysql statement.

 

Example:

 

$connection = new mysqli('server', 'username', 'password', 'database');
$result = $connection->prepare("SELECT products, usertype, special_pricing_user, special_pricing, pcconly FROM users WHERE username = ?");
$result->bind_param("s", $username);
$result->execute();
$result->bind_result($userproducts, $usertype, $special_pricing_user, $special_pricing, $pcconly);
while ($row = $result->fetch()) {
//
}

 

 

For us Noobs, can someone please break down what's going on in this form?? (in Lamens terms?)

 

Prepared Statements is just for PDO connections

 

its better to use it than connecting straight to mysql

in the beginning, its harder to use than mysql, but then its really easy, and safer

 

http://www.php.net/manual/en/book.pdo.php for more informations

google -> PHP PDO ftw

[Maq]

In addition to the above:

// Create a new mysqli object which is an interface to be specific
$connection = new mysqli('server', 'username', 'password', 'database');
// Invoke the prepare() method with the 'prepared' query string
$result = $connection->prepare("SELECT products, usertype, special_pricing_user, special_pricing, pcconly FROM users WHERE username = ?");
// Bind $username to the '?' value field that you see above
$result->bind_param("s", $username);
// Execute the query 
$result->execute();
// Fetch the results into the $result array
$result->bind_result($userproducts, $usertype, $special_pricing_user, $special_pricing, $pcconly);
// Loop through the results
while ($row = $result->fetch()) {

//
}

If that's too layman for you, then ask for specifics.

[KevinM1]

The best way to protect against MySQL injections in php is to use "Prepared" statements.  You don't need to validate the user input since it is completely separated from the mysql statement.

 

Example:

 

$connection = new mysqli('server', 'username', 'password', 'database');
$result = $connection->prepare("SELECT products, usertype, special_pricing_user, special_pricing, pcconly FROM users WHERE username = ?");
$result->bind_param("s", $username);
$result->execute();
$result->bind_result($userproducts, $usertype, $special_pricing_user, $special_pricing, $pcconly);
while ($row = $result->fetch()) {
//
}

 

 

For us Noobs, can someone please break down what's going on in this form?? (in Lamens terms?)

 

Prepared Statements is just for PDO connections

 

its better to use it than connecting straight to mysql

in the beginning, its harder to use than mysql, but then its really easy, and safer

 

http://www.php.net/manual/en/book.pdo.php for more informations

google -> PHP PDO ftw

 

And for MySQLi, which is what the code example shows....

[dougjohnson]

Take a look at this: http://php.net/manual/en/mysqli.prepare.php

 

What's going on here is the ?'s are place holders for the actual user variable input.  The bind_param "maps" the input to the corresponding "?" placeholder.  The binding "s" is for alphanumeric input, "i" would be for numeric and there are a couple other types see the link above.  The bind_result loads up the resulting values from the prepared mysql statement.  At first all of this is a pain, but after a while it's ok and you don't have to worry about injections.