Extracting data from mysql database then placing it in URLS?

Hi everyone!


I figured out hot to pass data from my mysql database into my URLS!


I just have one question relating to some of my code and info. in my database. In my database if a city is two words. For example, "Las Vegas" the current code below (which works for one worded cities) isn't working. What would I have to change in my script to allow for multiple worded cities? Thanks everyone!

http://whatsmyowncarworth.com/auto/boston
http://whatsmyowncarworth.com/auto/Las Vegas <<--- not echoing out information.
http://whatsmyowncarworth.com/auto/providence
http://whatsmyowncarworth.com/auto/miami
​

[QUOTE]<?php


ob_start(); // handle redirects


ob_end_flush();


include('init.php'); // connection to database


if (isset($_GET['u'])) {
    
    $city = mysql_real_escape_string($_GET['u']); 
     // protection against mysql injection
    if (ctype_alnum($city)) {
        $data = mysql_query("SELECT State, City FROM cars WHERE City='$city'" );
        if (mysql_num_rows($data) > 0) {
            while ($row = mysql_fetch_assoc($data)) {
                echo $row["City"];
            }
        }
    }
}
        
?>
[/QUOTE]

PHP:

 

Try something like this:

$city = mysql_real_escape_string(urldecode($_GET['u']));

PHP:

 

Hey Keith,

Thanks for the reply but it's not working.

http://whatsmyowncarworth.com/auto/Las%20Vegas

 

Yeah, sorry about that. ctype_alpha consideres the space character as non-alphanumeric. And of course %20 from the encoded URL is also non-alphanumeric. So the issue is that the string with a space isn't passing your validation. This should work:

<?php
include('init.php'); // connection to database

if (isset($_GET['u'])) {
	$city = mysql_real_escape_string(urldecode($_GET['u']));
	// protection against mysql injection
	if (ctype_alnum(str_replace(' ', '', $city))) {
		$data = mysql_query("SELECT State, City FROM cars WHERE City='$city'" );
		if (mysql_num_rows($data) > 0) {
			while ($row = mysql_fetch_assoc($data)) {
				echo $row["City"];
			}
		}
	}
}
?>

PHP:

$city will still contain the city with the space, but ctype_alnum will see it without the space.

 

Some advice:

1. mysql_real_escape_string is enough to prevent SQL injections, the ctype_alnum is excessive;
2. it's better to use MySQL PDO instead of mysql_query php.net/manual/en/ref.pdo-mysql.php
3. there's no need to check for mysql_num_rows, you can just run while right after the query.

 

MySQLi is also a better approach to preventing SQL injections. Hell, anything other than the standard mysql functions is better.

If you're using ctype strictly for SQL injection prevention, yeah it may be unnecessary .. but it is also my opinion that all external data should be validated/filtered. If I know that my "city" column should only contain alphanumeric characters, with maybe some spaces, then there is no reason why I shouldn't verify that the user input is of that format. I'd rather be able to gracefully provide the user with some error/alert than just return an empty result with no explanation.

 

Hi pmkbduvall and everyone else!

Thanks for helping with out with the code. I appreciate that! It's working great!

The only thing I'm trying to figure out now is the space in the URL. How would I get append a - to two lettered words. For example, las-vegas instead of Las%20Vegas. I'm assuming I would need to append a - in the cars.php syntax and not the .htaccess code correct?

Thanks everyone!

 

Check out the PHP manual for str_replace: http://us.php.net/manual/en/function.str-replace.php

You can do something like str_replace(' ', '-', $city) where ever you need to perform the replacement in your code. It should be pretty easy to figure out.

 

Exactly like Keith suggests... You should convert spaced to dashes for URLs. Then Convert dashes to Spaces before executing your SQL statement.

 

Don't forget, that some city names may contain hyphens (Sedro-Woolley, WA), apostrophes (O'Fallon, IL) and maybe other special characters.