10 Replies - 1845 Views - Last Post: 08 March 2014 - 11:29 AM

#1 Wolverine89   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 120
  • Joined: 14-January 14

Protect SQL injection

Posted 08 March 2014 - 01:05 AM

hi guys,

Is there anyway to protect the code below from SQL injection. So, statements like UNION and SELECT cannot be used in an URL to select data from other tables.

<?php

/**
 * Connect to database
 */
$connection = new mysqli('localhost', 'webshop', 'pass', 'webshop')
    or die('Cannot connect with MYSQL');

$query = 'SELECT name, image, description, price FROM products WHERE id = ' . $connection->real_escape_string($_GET['id']);

$result = $connection->query($query)
  or die('<div class="alert alert-danger">Query error: <pre>' . $connection->error . '</pre>Query: <code>' . $query . '</code> </div>');

$row = $result->fetch_array();

$connection->close();
?>



Thanks in advance,
Thijs

This post has been edited by Wolverine89: 08 March 2014 - 01:12 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Protect SQL injection

#2 Wolverine89   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 120
  • Joined: 14-January 14

Re: Protect SQL injection

Posted 08 March 2014 - 01:34 AM

Maybe i can use a mysql_real_escape_string before $query?
Was This Post Helpful? 0
  • +
  • -

#3 Ntwiles   User is offline

  • D.I.C Addict

Reputation: 148
  • View blog
  • Posts: 831
  • Joined: 26-May 10

Re: Protect SQL injection

Posted 08 March 2014 - 01:46 AM

You're not making full use of mysqli. With mysqli or pdo, you shouldn't use string concatenation to insert values. Use bindParam() instead. This will protect against SQL injection. Find out more here:

http://us3.php.net/p...ared-statements

This post has been edited by Ntwiles: 08 March 2014 - 01:46 AM

Was This Post Helpful? 2
  • +
  • -

#4 Wolverine89   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 120
  • Joined: 14-January 14

Re: Protect SQL injection

Posted 08 March 2014 - 07:55 AM

how can i use mysqli instead of pdo, how can create a better security?

do i need to change some statements? because pdo is a whole different technique!
Was This Post Helpful? 0
  • +
  • -

#5 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Protect SQL injection

Posted 08 March 2014 - 08:03 AM

The PHP reference manual has detailed documentation on MySQLi, including a page on prepared statements. - If you don't know how to access the docs, you're pretty much doomed to fail. It's the first thing any developer should learn; how to read the docs for the language(s) they are using. (Google is usually pretty good at finding doc pages. It's not rocket-science.)


No offence, but you don't really seem to understand what is going on in your own code. In order to have any hope of writing secure code, you first need to understand what your code is doing; how the function calls and APIs you are using work. For that, you need to research the documentation.

You ask if you can use "mysql_real_escape_string" before your query. The answer to that is no; you can't mix the APIs like that. However, if you look at your code, you'll notice that you are already running the ID through the MySQLi equivalent of that function. So your code is, in effect, as secure as old-school MySQL code using "mysql_real_escape_string" would be.

Of course, that is old thinking, and you really should be using prepared statements for your MySQLi code. I suggest you follow the link I posted in my first paragraph and read up on those.
Was This Post Helpful? 0
  • +
  • -

#6 Wolverine89   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 120
  • Joined: 14-January 14

Re: Protect SQL injection

Posted 08 March 2014 - 09:13 AM

I am going to use bind_param() but can you explain me what this means, i can not find usefull information

// The id
$_GET["id"] =  "1"; --> what does this 1 mean
 
// Bind the parameter, i --> int, datatype of column
$sql->bind_param('i', $_GET["id"]);



for example if the 1 only gets a record with id 1 i need to change that. THe database contains more records id 1 id 2 id 3 id 4

This post has been edited by Wolverine89: 08 March 2014 - 09:27 AM

Was This Post Helpful? 0
  • +
  • -

#7 Ntwiles   User is offline

  • D.I.C Addict

Reputation: 148
  • View blog
  • Posts: 831
  • Joined: 26-May 10

Re: Protect SQL injection

Posted 08 March 2014 - 09:22 AM

In your code, the value of $_GET['id']; has been hardcoded to "1". The author just did this as an example; normally the value would be passed via url.
Was This Post Helpful? 0
  • +
  • -

#8 Wolverine89   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 120
  • Joined: 14-January 14

Re: Protect SQL injection

Posted 08 March 2014 - 09:37 AM

That's exactly what i want! How can i pass the parameter through URL

so i just need this

$sql->bind_param('i', $_GET["id"]);

$id = $_GET["id"];

// Bind the parameter, i --> int, datatype of column
$sql->bind_param('i', $id]);
Was This Post Helpful? 0
  • +
  • -

#9 no2pencil   User is offline

  • Professor Snuggly Pants
  • member icon

Reputation: 6841
  • View blog
  • Posts: 31,533
  • Joined: 10-May 07

Re: Protect SQL injection

Posted 08 March 2014 - 10:00 AM

View PostWolverine89, on 08 March 2014 - 11:37 AM, said:

That's exactly what i want! How can i pass the parameter through URL

http://exmaple.com/?id=1
Was This Post Helpful? 0
  • +
  • -

#10 Wolverine89   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 120
  • Joined: 14-January 14

Re: Protect SQL injection

Posted 08 March 2014 - 10:21 AM

yeah whats with that
Was This Post Helpful? 0
  • +
  • -

#11 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Protect SQL injection

Posted 08 March 2014 - 11:29 AM

PHP Manual: Variables From External Sources

Quote

GET also applies to the QUERY_STRING (the information after the '?' in a URL). So, for example, http://www.example.com/test.php?id=3 contains GET data which is accessible with $_GET['id'].

// Assuming the URL is:
//   http://example.com/test.php?id=3
// Then:
echo $_GET["id"]; // = 3


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1