Parse a url and return database rows

Question

I'm trying to parse a url and connect to a database.

For instance when a user visits url.php?id=1 they should get a list of questions and answers related to that topic.

When I run the MySQL query

SELECT * FROM QuestionDB WHERE TopicID = 1

In phpmyadmin I get the desired rows.

Here is my code. I returns a blank document!

$topic = $_GET['id'];

$dbhost = 'host';
$dbuser = 'user';
$dbpass = 'pass';

$conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn ) {
  die('Could not connect: ' . mysql_error());
}

$sql = 'SELECT * FROM QuestionDB WHERE TopicID = '$topic'';
mysql_select_db('mydb');
$retval = mysql_query( $sql, $conn );

if(! $retval ) {
    die('Could not get data: ' . mysql_error());
}

while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
    echo "Question :{$row['Question']}  <br> ".
        "Answer : {$row['Answer']} <br> ".
        "Author : {$row['Author']} <br> ".
        "--------------------------------<br>";
}

echo "Fetched data successfully\n";

mysql_close($conn);

I can't work out what I'm doing wrong. If I delete the WHERE TopicID = '$topic' portion of my query, this code does print out all the rows from my database.

Cheers in advance

Use `mysqli_` or PDO. The `mysql_` extension is deprecated and insecure. — Difster, Jan 31 '18 at 21:37
As in someone could go to file.php?id=SOMESQL and do bad things? — Richard Tock, Jan 31 '18 at 21:58

Syscall · Accepted Answer · 2018-01-31T21:52:19.993

0

You have a syntax error. The string concatenation with the variable fails.

You have to change this line :

$sql = 'SELECT * FROM QuestionDB WHERE TopicID = "'.$topic.'"';

or

$sql = "SELECT * FROM QuestionDB WHERE TopicID = '$topic'" ;

Important note : Your code is vulnerable to SQL injections and may compromise the security of your database. You should use PDO or mysqli APIs to secure your SQL queries, and using prepare function.

edited Jan 31 '18 at 21:52

answered Jan 31 '18 at 21:36

Syscall

19,327
10
37
52

Thanks. This no longer leaves me with a blank page. I get "Fetched data successfully". I assume in PHP i need to be careful with ' and ". It still doesn't give me any data, though! EDIT - Your edit ($sql = "SELECT * FROM QuestionDB WHERE TopicID = '$topic'") worked. Thank you. Why did that work, but $sql = 'SELECT * FROM QuestionDB WHERE TopicID = "$topic"'; didn't , I wonder? – Richard Tock Jan 31 '18 at 21:38
1

@RichardTock Try to `var_dump($topic)` to see what's wrong. – Syscall Jan 31 '18 at 21:40
Please add a warning that this may be vulnerable to SQL Injection. – GrumpyCrouton Jan 31 '18 at 21:42
@GrumpyCrouton Already added in comments but I will add. Thanks. – Syscall Jan 31 '18 at 21:43
1

@Syscall Comments are very temporary, and can be removed for many reasons randomly. It's better to add it to the question so that future vistors who actually view the answer will see that there is a security hole. Thank you for editing. – GrumpyCrouton Jan 31 '18 at 21:45
@GrumpyCrouton I'm agree. Thanks. – Syscall Jan 31 '18 at 21:47
@RichardTock I didn't see your update. I've updated my anwser between you get my first anwser (badly wrote), sorry. Both lines in anwser should works. Please check updated first line. – Syscall Jan 31 '18 at 21:50

Parse a url and return database rows

1 Answers1