0

lets say, i want to create a log in query:

$query ="SELECT * FROM userstable WHERE uName='".$userName."' AND pass='".$password."' ";
$result = mysql_query($query);

$num =  mysql_numrows($result);
if ($num == 0) {
echo "wrong detiales";
}
else
{
echo "in";

but this way, the clients get to reach and put his data inside my query... I don't like it... why give the client access to fetch data from my db?

question: will it be healthy to put my data in an array, and then search the array instead of searching the query? it will probably be a tad slower (let's say i don't have many records). even if it's a tad slower, will it be more secure?

and suggestions? code examples?

10x, (by the way, I love this site so much!)

Strawberry
  • 33,750
  • 13
  • 40
  • 57
nmn
  • 9
  • 1
  • 1
    I'd suggest to use mysqli or PDO and query preparation... – bwoebi Sep 06 '13 at 15:25
  • 1
    Kudos for recognizing this as a security issue. There's actually a technical term for this, "SQL injection". Someone with a bit spare time than I can probably write a good answer, but a Google search on the term should give you plenty of reading on the topic in the meantime. – lc. Sep 06 '13 at 15:30
  • What's the difference between running the query with values provided by the client vs. selecting *all* records and then comparing them to the client values (injection aside) – Explosion Pills Sep 06 '13 at 15:33
  • You are right that you are leaving yourself wide open to SQL injection attacks. Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started. – Andy Lester Sep 06 '13 at 15:37

1 Answers1

1

will it be healthy to put my data in an array, and then search the array instead of searching the query?

NO

It will spoil the whole purpose of the database. Which main purpose is to serve you with the only data needed, doing all the data mining inside. That's the point.

So, it's just unacceptable solution. Although secure, it's absolutely inefficient and unreliable.

Luckily, there is already a proper way to run queries secure - prepared statements. You may read of them in the famous question and get more practical details from PDO tag wiki

Namely, you have to make it

$query = "SELECT 1 FROM userstable WHERE uName=? AND pass=?";
$stmt  = $pdo->prepare($query);
$stmt->execute(array($userName, $password));
$found = $stmt->fetchColumn();
if (!$found)
{
    echo "wrong detiales";
}
else
{
    echo "in";
}
Community
  • 1
  • 1
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • thanks for the reply! I have to deploy this code on a server that cant run prepared statements for some reason. so i have to be creative. – nmn Sep 11 '13 at 18:01
  • There is always a way to run prepared statements. So, you indeed have to be creative - to make them available. – Your Common Sense Sep 11 '13 at 18:23