ansaurus

Question

Answer 1

A:

Try this:

$q = "SELECT id FROM table WHERE id = '".mysql_real_escape_string($_POST['user_id'])."'";

$r = mysql_query($q);

if(mysql_num_rows($r) > 0) {
    echo "User ID was found in table";
} else {
    echo "User ID was not found in table";
}

Tatu Ulmanen 2010-01-02 21:04:08

It's better to use parameters than string concatenation to create queries. It's not just about SQL injection. It also helps the optimizer.

Mark Byers 2010-01-02 21:08:17

Yea, but given that we're dealing with a complete novice here and you didn't explain how to pass the actual variable to the query, I think this might be more helpful.

Tatu Ulmanen 2010-01-02 21:20:05

Teaching people how to do it in not the best way is arguably not very useful either. It's easy to make a mistake that leaves a vulnerability and IMHO the code is harder to read without parameters. I guess I can add information to my comment about how to use parameters but it would fill the page. I think if he doesn't know how to use parameters already he should ask a new question or search for one of the many question already here on the topic.

Mark Byers 2010-01-02 21:26:24

didnt really work...<b>Warning</b>: mysql_num_rows(): supplied argument is not a valid MySQL result resource in

amit 2010-01-02 21:30:08

@amit: Did you change the tablename from `table` to the name of your actual table?

Mark Byers 2010-01-02 21:31:18

@Mark Byers, in my opinion, one should always learn to do the 'wrong' way first to understand *why* it is wrong. Not that there would be anything fundamentally wrong with what I showed though. And if information on how to use parameters would fill a page, don't you think that's too much at this point for a guy that's just starting with SQL? What about other ways of preparing statements, like through sprintf? Should we teach him that too, as that is a perfectly viable alternative to for example PDO's prepared statements? As a beginner, keep thing as simple as possible.

Tatu Ulmanen 2010-01-02 21:37:49

thanks it worked. i typo'd the id.

amit 2010-01-02 21:39:53

@Mark: i didnt use the string concatenation. i had the $_POST in a variable and just used that. but i still am not sure if this can totally avoid SQL injection?

amit 2010-01-02 21:42:47

@amit, you have to use the `mysql_real_escape_string` function on the variable to safely use it in a SQL query.

Tatu Ulmanen 2010-01-02 21:46:20

@Tatu: There's no point me filling a page not because it's not worth reading, but because it has already been explained on many other pages far better than I can explain it. I would recommend reading the question and accepted answer that I linked to from my answer. I don't think it's too much to learn for a person who is new to PHP.

Mark Byers 2010-01-02 22:45:08

PS, it's not just about SQL vulnerabilities. Doing it this way requires unnecessary extra processing to convert the data to and from a string, and also will be slower because the queries can't be cached. Using parameters is the recommended way to execute SQL queries.

Mark Byers 2010-01-02 22:49:55

Answer 2

A:

$qryResult = mysql_query("SELECT userid FROM idtable WHERE userid='$theIdOfUser'");
if (mysql_num_rows($qryResult) == 0)
{
// add a new vote
}
else
{
// notify the user that double voting is prohibited
}

Try not to use names like "id" to avoid conflicts with reserved words and related complications and need to use quotes

alemjerus 2010-01-02 21:05:09

Use parameters. Even though $theIdOfUser is presumably an integer, so the risk of SQL injection is low, it's still not a good idea to do this.

Mark Byers 2010-01-02 21:10:20

ansaurus

tags:

views:

answers:

mysql compare table values

related questions