1

Hi guys I have a program built using mysql_* and I am trying to convert it to PDO for security and depreciative reasons

So I have a load of mysql_* functions setup like

return select_from_where('users', '*', "username = '$username' AND password = '$pass'", "LIMIT 1");

Which I have converted to PDO

return $conn -> query("SELECT * FROM users WHERE username = '$username' AND password = '$pass' LIMIT 1");

However the program does not feed the right result, I'm not sure if it is even returning data

My question is, do I have to set the PDO response to a variable that I can then use, or is it possible to have it return values which I can use in my program using a similar method to above?

I have included global $conn for each function query so I'm sure it is connecting like it should, its just not feeding the result as intended..

Does anyone have a quick fix for this issue as my program is almost done and is pending release :D

Thanks in advance

Luke

** EDIT LINE *

$sql = ("SELECT * FROM users WHERE username = '$username' AND password = '$pass' LIMIT   1");
$stm = $conn->prepare($sql);
$stm->execute(array($username,$pass)); $user = $stm->fetch(); echo $user['username'];
Coderrrr
  • 230
  • 1
  • 16
  • 3
    `select_from_where` is not a PHP core function. Probably it does combination of `mysql_query` and `mysql_fetch_*`. You'd need to fetch rows in PDO. – Voitcus May 14 '13 at 13:52
  • 3
    Using PDO this way does NOT make it secure. – hank May 14 '13 at 13:59

2 Answers2

5

First, Personally I see no point in having a function like select_from_where

You actually save yourself nothing - you just moved words "SELECT, FROM and WHERE" from query to function name, yet made this function extremely limited - say, no joins or stuff.

Second, PDO::query() function shouldn't be used anyway - it doesn't support prepared statements.

So, the code have to be

global $conn;
$sql = "SELECT * FROM users WHERE username = ? AND password = ? LIMIT 1";
$stm = $conn->prepare($sql);
$stm->execute(array($username,$pass));
return $stm->fetch();

You have to also configure your PHP and PDO in order to be able to see every error occurred.

Community
  • 1
  • 1
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • Based on that do you think it is even worth having the functions at all, I actually hired someone to configure some stuff on my site and they 'updated' from PDO to mysql_* so I have to go back and change it.. Each function to connect to the database is kept in one file to keep it organised, I have the connection to the database in 2 separate files, one to set the database username and password, the other to actually connect using the preset username and password variables. – Coderrrr May 14 '13 at 14:13
  • If I change the program so that pdo connections are only found where they are used ie in the actual program code do you think that would be a better idea, or can I use the code you have given above in the form of a function which is called everytime I need to access that part of the database? And as it is will it feed out data as it is suppost to? I apologise for my ignorance I am no PHP guru, clearly, but I am trying to learn and get my head around the connection types, especially PDO – Coderrrr May 14 '13 at 14:19
  • @LukeWatson yes, functions are great in general. But PDO is quite good with such trivial queries, so, I don't think you need one to run queries – Your Common Sense May 14 '13 at 14:26
  • Okay just for clarification - using queries is BAD because the statement is not prepared and therefore vulnerable to SQL injection, so using prepared querys prevents this as they cannot directly edit the sql query? If I'm not using functions how do I access the data from the query, I will post what I've done (which is not working) to see if you know how to fix it, it is part of a larger function Login() – Coderrrr May 14 '13 at 14:53
  • @LukeWatson You need to read an answer from the link I posted and configure your PHP and PDO the right way. That's first and foremost thing to do. Then you need to use a query from my answer, not one from your question. Note the ? marks – Your Common Sense May 14 '13 at 15:02
  • 1
    Ah I see sorry, you set the ? mark and in the execute you tell it what data to use, that makes sense :) I have my program almost working now, thanks so much for your help – Coderrrr May 14 '13 at 15:11
0

Change this

return $conn -> query("SELECT * FROM users WHERE username = '$username' AND password = '$pass' LIMIT 1");

to:

$username = 'user';
$password ='password';
$stmt =$conn->prepare("SELECT * FROM users WHERE username = ? AND password = ? LIMIT 1");
$stmt->execute(array($username, $password));
echo $stmt->rowCount();