PHP variable

[Macman1993]

So I decided a good way to really test my one day old MYSQL skills would be to write a random fact generator that uses a mysql database that stores two columns one is the id of the fact and the other is the fact. I use the php rand() function to generate a random id (between 1 and the highest id). My generated number is working fine and I store it in the variable $rand but the problem comes when trying to get the row with the id of $rand. I use the following query to get the row with the id

$query=mysql_query("SELECT * FROM facts WHERE id='2'") or die(mysql_error());

then a few more lines to get the fact. This works perfectly for a hard coded number but when I try to use the value of $rand it just doesn't work. How do I use a php int in a mysql query? I want something like this

$query=mysql_query("SELECT * FROM facts WHERE id='$rand'") or die(mysql_error());

I'm sure this is easy to do but I just don't know off the top of my head. I tried casting it with int($rand) just got the sake of it but also no luck. Anyone have the solution for using the value of $rand for my id?

 

[pfealey]

$sql = "SELECT * FROM facts WHERE id = " . $rand;

$query=mysql_query($sql) or die(mysql_error());

You could try the above, i'm not near my LAMP box to test this though

 

[m7a7d]

I'm definitely not a PHP expert (not even near), but what I've been taught so far is to build the query as a string, then use the mysqli_query() function.

$query = "SELECT * FROM facts WHERE id = '$rand'";
$result = mysqli_query($query);

But honestly there is no difference, now that I think about it

 

[Darth.Titan]

Is rand() possibly returning a value that's outside the number of entries in your facts table?
When I execute rand() with no arguments, I usually get large numbers like 752241300. Make sure to use the limiting arguments in rand() to prevent this.
If you have 100 entries in your fact table use

$rand = rand(1,100);

If you're already doing this, try what m7a7d suggested and load the query into a string first. That way you can echo it out to see what is trying to run. That usually helps me spot problems in queries.

 

[SrWebDeveloper]

$range_result = mysql_query( " SELECT MAX(`id`) AS max_id ,  MIN(`id`) AS min_id FROM `facts` ");

 $range_row =  mysql_fetch_object( $range_result ); 
 
$random = mt_rand(  $range_row->min_id , $range_row->max_id );
 
$result =  mysql_query( " SELECT * FROM `facts` WHERE `id` >= $random LIMIT 0,1  ");

This method checks min/max record numbers, picks a random within that range and account for missing id's and ensures only one row is returned. Using PHP is better than MySQL's internal RAND() because MySQL creates a temporary table with all the result rows and assigns each one of them a random sorting index. The results are then sorted and returned, which can be slow when large numbers of records are involved. I know this is a small project, so you could use MySQL all the way, honestly, but the disclaimer is worth noting for those who are considering methods and using large data sets. FYI.

-jim

 

[Cromulent]

I'm definitely not a PHP expert (not even near), but what I've been taught so far is to build the query as a string, then use the mysqli_query() function.

$query = "SELECT * FROM facts WHERE id = '$rand'";
$result = mysqli_query($query);

But honestly there is no difference, now that I think about it

That is extremely bad practice. If you have dodgy data stored in your variables you could potentially destroy your database using that technique. I wish I knew who taught things like this.

Use parameterised queries instead.

 

[m7a7d]

That is extremely bad practice. If you have dodgy data stored in your variables you could potentially destroy your database using that technique. I wish I knew who taught things like this.

Use parameterised queries instead.

Could you clarify?

You mean, building a string in $query and using it as the argument to mysqli_query() is bad practice?

I'm a newb and I'm having trouble understanding the difference between

$query = 'SOME STRING';
$result = mysqli_query('$query');

and

$result = mysqli_query('SOME STRING');

 

[rowsdower]

Could you clarify?

You mean, building a string in $query and using it as the argument to mysqli_query() is bad practice?

It is bad practice to insert variables directly into the queries. See this article (not MySQL specific, but the concept is the same.

I'm a newb and I'm having trouble understanding the difference between

$query = 'SOME STRING';
$result = mysqli_query('$query');

and

$result = mysqli_query('SOME STRING');

There is no difference between these two. The problem comes in when you add a variable like this:

$result = mysqli_query("select firstname,lastname from users where username='$username'");

Depending on what is in $username, an attacker could delete everything in your database, even for a query like that one that looks like a simple select.

 

[racketeer71]

$range_result = mysql_query( " SELECT MAX(`id`) AS max_id ,  MIN(`id`) AS min_id FROM `facts` ");

 $range_row =  mysql_fetch_object( $range_result ); 
 
$random = mt_rand(  $range_row->min_id , $range_row->max_id );
 
$result =  mysql_query( " SELECT * FROM `facts` WHERE `id` >= $random LIMIT 0,1  ");

Say you have the following values in the id column:

1
2
4
5
8

(The missing ones, are rows that were deleted when it was discovered that they weren't true).

You'll get a MIN=1 and MAX=8, so I believe you could get ie. 3, 6 or 7 even though they have been deleted.

The result being, that your last query might return 0 rows.

 

[racketeer71]

Could you clarify?
I'm a newb and I'm having trouble understanding the difference between

$query = 'SOME STRING';
$result = mysqli_query('$query');

and

$result = mysqli_query('SOME STRING');

There's no difference, they're both equally bad.

Imagine you have a login form, where the user submit $username and $password.

You then create something like this:

$query = "SELECT ID FROM Users WHERE Username = '" . $username . "' AND Password = '" . $password. "'

So, if I login with $username = "foo" and $password = "bar", the query being executed would look like this:

SELECT ID FROM Users WHERE Username = 'foo' AND Password = 'bar'

So that's OK. But imagine I login with:
username = "foo"
password = ' OR '1' = '1

Then the query being executed looks like this:

SELECT ID FROM Users WHERE Username = 'foo' AND Password = '' OR '1' = '1'

- and then I'll get access without knowing username nor password, since '1' = '1' always.

Look up SQL injection attack in your favorite search engine.

The problem kan be mitigated with http://php.net/manual/en/function.mysql-real-escape-string.php

 

[SrWebDeveloper]

It is bad practice to insert variables directly into the queries. See this article (not MySQL specific, but the concept is the same.

Respectfully, that is an outrageous and completely false statement in the context in which you presented it. The article focuses on the very true premise that "the risk of the user supplying input that may cause your query to do something that you had not expected". The article actually includes a link about SQL injection prevention which is the solution to said concerns, mirroring what racketeer71 said in their response and I echo now.

It is irresponsible to twist a valid concern about corrupting a database into a broad, general all-encompassing statement that inserting variables is bad, without any context as to why. The correct statement might be, "It is a security risk to insert user populated variables into a database query without following simple anti-SQL injection techniques before embedding those variables."

Come on, now!



-jim

 

[harpster]

I'm sure this is easy to do but I just don't know off the top of my head. I tried casting it with int($rand) just got the sake of it but also no luck. Anyone have the solution for using the value of $rand for my id?

Your code should work assuming you have an id in the database equal to the value of $rand. Just test your variable with a fixed number first then use your computed random number.

Also make sure for your test environment that in your php.ini file you have display_errors = On so you can see why things might have failed.

$rand = 2; //test with this then replace with your rand function.

echo "random number = " . $rand . "<br>";
 
$query=mysql_query("SELECT * FROM fact WHERE id='$rand'") or die(mysql_error());  

$row=mysql_fetch_array($query);
echo $row[1];

 

[rowsdower]

Respectfully, that is an outrageous and completely false statement in the context in which you presented it. The article focuses on the very true premise that "the risk of the user supplying input that may cause your query to do something that you had not expected". The article actually includes a link about SQL injection prevention which is the solution to said concerns, mirroring what racketeer71 said in their response and I echo now.

It is irresponsible to twist a valid concern about corrupting a database into a broad, general all-encompassing statement that inserting variables is bad, without any context as to why. The correct statement might be, "It is a security risk to insert user populated variables into a database query without following simple anti-SQL injection techniques before embedding those variables."

I don't know about completely false. Now that I read it again, it certainly needs some context and is more general than I intended it to be. I was intending to refer specifically to inserting parameters into a query, but that isn't really clear from my statement.

There are certainly safe ways to concatenate strings or use string substitutions with variables to construct a query (including the code you presented, since the variable came from a trusted source). However, my opinion is that it is always a good practice to construct the query using '?' placeholders and use parameters to fill in the values. That way you always force a distinction in your mind between the variables you are using for the structure of the query and the variables that could possibly have come from the user, and you don't accidentally end up placing data from the user directly into a query somewhere down the line (e.g. if the OP some day wants the user to be able to bookmark his favorite facts or something and ends up using an argument to do it).

I agree that my original statement was not entirely true as written, and that your rewording of my statement is entirely true. However, I think my statement is more likely to cause a "should I be doing this" moment, which I think is a good thing.

Anyway, apologies if I caused any confusion.

 

[m7a7d]

As an application programmer new to web programming, I appreciate this discussion. Thanks for everyones input.

Thanks racketeer71, you explained it perfectly

 

[SrWebDeveloper]

However, I think my statement is more likely to cause a "should I be doing this" moment, which I think is a good thing.
Anyway, apologies if I caused any confusion.

Your intentions - good.
Your execution of said intentions - red alert!

The object is to educate users as to vulnerabilities, not scare them to death! Relax, I'm not angry or whatever, and I was careful to quote only the portion of your statement I had issues with.

-jim

 

[SrWebDeveloper]

You'll get a MIN=1 and MAX=8, so I believe you could get ie. 3, 6 or 7 even though they have been deleted.
The result being, that your last query might return 0 rows.

Incorrect.

Look again at my example code which includes this:

WHERE `id` >=

The "greater than or equal to" accounts for any missing ID's.



I posted that example for good reason.

-jim

 

[harpster]

More info on Selecting Random Records (Min Max stuff) here...

http://akinas.com/pages/en/blog/mysql_random_row/

 

[m7a7d]

I've been thinking about this recently..

about my original suggestion to put the query in a variable. Is this considered bad practice no matter how it's used?

say I have a function,

    function get_all_subjects() {
        global $connection;
        $query = "SELECT *
                FROM subjects
                ORDER BY position ASC";
        $subject_set = mysqli_query($connection, $query);
        confirm_query($subject_set); // another user-defined function
        return $subject_set;
    }

that I call in almost all my files. Is this bad practice? I understand you should be validating user input data before sending it off to query, but I don't see what's wrong with hard coding a string to use for a query.

 

[angelwatt]

Is this bad practice? I understand you should be validating user input data before sending it off to query, but I don't see what's wrong with hard coding a string to use for a query.

No, hard-coded is fine security-wise. It may become a nuisance to update down the road since it would require the developer to poor through all the code looking for this query, but that's about it.