1

I currently have a Get varible

$name = $_GET['user'];

and I am trying to add it to my sql statement like so:

$sql = "SELECT * FROM uc_users WHERE user_name = ". $name;

and run

$result = $pdo -> query($sql);

I get an invalid column name. But that doesn't make sense because if I manually put the request like so

$sql = "SELECT * FROM uc_users WHERE user_name = 'jeff'";

I get the column data, just not when I enter it as a get variable. What am I doing wrong. I am relatively new to pdo.

Update: Now I have the following:

$name = $_GET['user'];

and

$sql = "SELECT * FROM uc_users WHERE user_name = :name";
    //run the query and save the data to the $bio variable
    $result = $pdo -> query($sql);
    $result->bindParam( ":name", $name, PDO::PARAM_STR );
    $result->execute();

but I am getting 

> SQLSTATE[42000]: Syntax error or access violation: 1064 You have an
> error in your SQL syntax; check the manual that corresponds to your
> MySQL server version for the right syntax to use near ':name' at line
> 1
RedShirt
  • 855
  • 2
  • 20
  • 33

4 Answers4

6

For your query with the variable to work like the one without the variable, you need to put quotes around the variable, so change your query to this:

$sql = "SELECT * FROM uc_users WHERE user_name = '$name'";

However, this is vulnerable to SQL injection, so what you really want is to use a placeholder, like this:

$sql = "SELECT * FROM uc_users WHERE user_name = :name";

And then prepare it as you have:

$result = $pdo->prepare( $sql );

Next, bind the parameter:

$result->bindParam( ":name", $name, PDO::PARAM_STR );

And lastly, execute it:

$result->execute();
Nick Coons
  • 3,682
  • 1
  • 19
  • 21
  • scratch that now it is saying "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':name' at line 1" – RedShirt Jan 13 '14 at 06:37
  • When using the `:name` placeholder instead of the actual variable, don't surround it in single quotes. – Nick Coons Jan 13 '14 at 06:41
  • I didn't surround it in single quotes it was working but then it started spitting out that error – RedShirt Jan 13 '14 at 06:45
  • So you're saying that it was working, and then without making any changes it just stopped working? – Nick Coons Jan 13 '14 at 06:46
  • yes it just stopped. I used the code you suggested using the :name as a placeholder. I just updated my code – RedShirt Jan 13 '14 at 06:52
  • I believe I found the issue and updated my answer accordingly, it should be `$result = $pdo->prepare($sql);` instead of `$result = $pdo->query($sql);` (I copied and pasted some of your code without proof-reading, sorry about that). – Nick Coons Jan 13 '14 at 07:05
0

I find this best for my taste while preventing SQL injection:

Edit: As pointed out by @YourCommonSense you should use a safe connection as per these guidelines

// $conn = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

$sql = 'SELECT * FROM uc_users WHERE user_name = ?';
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $name);
$stmt->execute();
$result = $stmt->get_result();
$stmt->close();

// perhaps you'll need these as well
$count = $result->num_rows;
$row = $result->fetch_assoc();

/* you can also use it for multiple rows results like this
while ($row = $result->fetch_assoc()) {
    // code here...
} */

BTW, if you had more parameters e.g.

$sql = 'SELECT * FROM table WHERE id_user = ? AND date = ? AND location = ?'

where first ? is integer and second ? and third ? are string/date/... you would bind them with

$stmt->bind_param('iss', $id_user, $date, $location);
/*
 * i - corresponding variable has type integer
 * d - corresponding variable has type double
 * s - corresponding variable has type string
 * b - corresponding variable is a blob and will be sent in packets
 */

Source: php.net

EDIT:

Beware! You cannot concatenate $variables inside bind_param

Instead you concatenate before:

$full_name = $family_name . ' ' . $given_name;
$stmt->bind_param('s', $full_name);
Community
  • 1
  • 1
s3c
  • 1,481
  • 19
  • 28
  • That's a correct answer *per se*, but it is rather out of line: the question is about PDO and your answer is about mysqli – Your Common Sense Apr 01 '20 at 10:36
  • Also, FYI, just a single line for the connect is [not enough](https://phpdelusions.net/mysqli/mysqli_connect) – Your Common Sense Apr 01 '20 at 10:38
  • @YourCommonSense thank you for your input. I never even thought about the difference. Only wanted to get rid of SQL injection attack possibilities. Edited your link to post. – s3c Apr 02 '20 at 11:05
-1

Try this .You didn't put sigle quote against variable.

$sql = "SELECT * FROM uc_users WHERE user_name = '". $name."'";

Note: Try to use Binding method.This is not valid way of fetching data.

Mahmood Rehman
  • 4,303
  • 7
  • 39
  • 76
-1
$sql = "SELECT * FROM  'uc_users'  WHERE user_name = '". $name."' ";
Pupil
  • 23,834
  • 6
  • 44
  • 66