Is my select statement secure enough?

chrisguk

I have thrown together the following, with a few snippets pulled from various places. Is it ok to use without the worry of sql injection?


try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Define and perform the SQL SELECT query
  $sql = "SELECT * FROM `db_linux`";
  $result = $conn->query($sql);

  // If the SQL query is successfully performed ($result not false)
  if($result !== false) {
    $cols = $result->columnCount();           // Number of returned columns

// Parse the result set
   if ($sql->num_rows > 0) {
    while ($row = $sql->fetch_object())
    {
        $plan = $row->plan;
        $name = $row->name;
        $text = $row->text;

}

} else {


$plan = 'no plan';
$name = 'NA';
$text = 'NA2';

} 


  }

bradgrafelman

Er... do you even know what SQL injection is? Sorry to be blunt, but it seems a prudent question given that you don't even appear to use any user-supplied data in either of the two SQL queries you're executing above.

Then there is of course the issue of why you're SELECT'ing an entire table followed by a while() loop that wastes resources since you only end up with the very last row's contents. Are you perhaps missing at least a WHERE clause for that query?

chrisguk

Ok, Ill be blunt, I haven't a clue, thats why I was hoping for some assistance 😉 Basically I want to select the data and use the variables in a web page. All I ultimately want is of course for it to work and not to be open to any form of attack.

Any help will be great 😉

I see what you mean about the "while". How can I amend this not to include while?

bradgrafelman

chrisguk;11028111 wrote:
Ok, Ill be blunt, I haven't a clue, thats why I was hoping for some assistance 😉 Basically I want to select the data and use the variables in a web page. All I ultimately want is of course for it to work and not to be open to any form of attack.

Well "any" form of attack is rather broad. For the scope of SQL injection attacks, you might start by reading the PHP manual page [man]security.database.sql-injection[/man].

chrisguk;11028111 wrote:
I see what you mean about the "while". How can I amend this not to include while?

Simply remove the while() construct (keeping its condition statement and body, of course). However, that still would have a fundamental problem in that you're never telling the SQL server which row from the table you want. (I also like to add a "LIMIT 1" to the end of the query in an attempt to save resources and, at the very least, self-document my code - e.g., make it obvious that I only expect to use at most one rows from the result set.)

Weedpacket

bradgrafelman wrote:
Well "any" form of attack is rather broad.

For example, that code does not protect against $5 wrenches.

sneakyimp

chrisguk, I applaud your desire to make your queries safe from attack. So many folks just want to get something done without any regard for security and that's why things are so easy to hack.

As a general rule, attacks target code that deals with user input. You can't hack a server without writing data to it at some stage -- either during software install or when making a request (HTTP, MySQL, ssh, etc.) to send data to/from the server.

Your script above does not stick any user input into your query so the query will always be the same exact query. It's when you start taking user input and modifying your query that you need to watch out. There are two major techniques to make this safe:
1) Validate the user input to make sure it is actually a number or email address or whatever before you stick it in your query. (helpful functions are [man]filter_var[/man] and [man]preg_match[/man])
2) Either escape the user input with something like mysqli's escape function or use prepared statements which take care of escaping data for you.

bradgrafelman

sneakyimp;11028221 wrote:
escape the user input with something like mysqli's escape function

... unless the user input is anything other than a string, in which case that function isn't appropriate and other methods (such as casting) should be used, ...

😉

chrisguk

I really appreciate the help I have got so far. My problem is I don't have the time to study MySQL and PHP in more depth so I have to pick things up as I go along. I made a post also in the general area about my next stage. I'm learning to ride a bike here but I keep falling off, a little metaphor there.

Basically what I am trying to achieve is to make some kind of mini CMS, in that some information is displayed that I can administrate from the db.

It's a long hard road

sneakyimp

chrisguk;11028229 wrote:
It's a long hard road

It's easier to make progress if you ask limited questions about specific tasks in PHP. Broad questions usually just piss everyone off.

Bonesnap

sneakyimp;11028231 wrote:
Broad questions usually just piss everyone off.

And you get broad answers.

annaharris

Your select statement is not 100% secure.

sneakyimp

annaharris;11028859 wrote:
Your select statement is not 100% secure.

Actually, the query in the example is 100% secure because it doesn't use any user input. Annaharris, if you are going to spam your web dev url by making useless posts, they should probably be accurate.

bradgrafelman

sneakyimp;11028883 wrote:
they should probably be accurate.

... or else they'll continue to be deleted by myself. 😉 (Sorry, I missed this one.)