MySQL query based on user input

Question

I have a DB table. I want to make a text input where the user can input the "uid" and the query will return the row associated with that uid.

So let's say I have something like this:

$query = "SELECT name,age FROM people WHERE uid = '2' LIMIT 0,1";
$result = mysql_query($query);
$res = mysql_fetch_assoc($result);

echo $res["age"];

how would I modify that query to something like..

SELECT name, age 
  FROM people 
 WHERE uid = $_POST['blahblah'] LIMIT 0,1

Thanks in advance for your help!

gahooa · Accepted Answer · 2012-07-30 04:02:51Z

5

In reality...

// Read input from $_POST
$uid = (isset($_POST['uid']) ? $_POST['uid'] : '');

// Build query.  Properly escape input data.
$query = 
  "SELECT name,age " .
  "FROM people " .
  "WHERE uid = '" . mysql_real_escape_string($uid) . "' " . 
  "LIMIT 0,1";

Its advisable to escape characters in the variable for security reasons. Take a look at this document for some of the reasons:

http://en.wikipedia.org/wiki/SQL_injection

edited Jul 30, 2012 at 4:02

gahooa

138k14 gold badges101 silver badges104 bronze badges

answered Jul 30, 2012 at 3:54

Matt Lo

5,7511 gold badge25 silver badges23 bronze badges

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

1 Comment

NewInTheBusiness Over a year ago

Or why not switching to PDO + use prepared statements to make it safe from SQL injection?

vulcan raven · Accepted Answer · 2012-07-30 04:03:21Z

3

To save from SQL injection attack, use:

$search_query = mysql_real_escape_string($_POST['blahblah']);

$query  = "SELECT name, age FROM people WHERE uid = '".$search_query."' LIMIT 0 , 1";

edited Jul 30, 2012 at 4:03

answered Jul 30, 2012 at 3:58

vulcan raven

33.8k11 gold badges61 silver badges101 bronze badges

Comments

Rajan Rawal · Accepted Answer · 2012-07-30 04:03:50Z

0

There are so many ways to do the same But first escape it and store it in one variable

$blahblah = mysql_real_escape_string($_POST['blahblah']);

And then There are

First: As @Mett Lo mentioned:

$query = "SELECT name,age FROM people WHERE uid = '" . $blahblah . "' LIMIT 0,1";

Second:

$query = "SELECT name,age FROM people WHERE uid = '{$blahblah}' LIMIT 0,1";

Third:

$query = "SELECT name,age FROM people WHERE uid = '$blahblah' LIMIT 0,1";

and if blahblah is an int value in db table then Fourth:

$query = "SELECT name,age FROM people WHERE uid = $blahblah LIMIT 0,1";

answered Jul 30, 2012 at 4:03

Rajan Rawal

6,3236 gold badges44 silver badges62 bronze badges

2 Comments

cream Over a year ago

for my own educational purposes, why was this -1ed? also, i was under the impression that if a string was enclosed in double quotes it you could insert a variable with no quotes. is this true, or is that only for echos? i appreciate your suggestions/answers.

Rajan Rawal Over a year ago

@HiggsBoson who did -1 they should add comment. If you have mentioned something as int in db table then you need not to put quotes around that variable. More over adding quotes affects to data insert speed. But where db table have varchar or char columns or date or other than int then you must enclose it with ("") double quotes

Vivek · Accepted Answer · 2012-07-30 04:06:51Z

0

You may use the sprintf function to create the query.

$query = sprintf("SELECT name,age FROM people WHERE uid = '%s' LIMIT 0,1",
         $_POST['blahblah'] );

The rest will be the same. It is highly recommended that you escape the $_POST data before running the query to prevent SQL attacks. You may re phrase the query as follows.

$query = sprintf("SELECT name,age FROM people WHERE uid = '%s' LIMIT 0,1",
         mysql_escape_string($_POST['blahblah']) );

answered Jul 30, 2012 at 4:06

Vivek

2,7651 gold badge21 silver badges21 bronze badges

Collectives™ on Stack Overflow

MySQL query based on user input

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related