efficient and secure queries idea - mysql

Question

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!)

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.
– lc., Commented Sep 6, 2013 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
– Explosion Pills, Commented Sep 6, 2013 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. bobby-tables.com/php has examples to get you started. — Andy Lester
– Andy Lester, Commented Sep 6, 2013 at 15:37

Community · Accepted Answer · 2020-06-20 09:12:55Z

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";
}

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 6, 2013 at 15:43

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nmn Over a year ago

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.

Your Common Sense Over a year ago

There is always a way to run prepared statements. So, you indeed have to be creative - to make them available.

Collectives™ on Stack Overflow

efficient and secure queries idea - mysql

1 Answer 1

NO

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

NO

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related