Best use of $_GET with a URL

[ultraloveninja]

I'm working on using a MySQL query to return some resutls based off getting part of the URL from a previous page.

 

 

URL sample:

 

<a href="http://site.com/file.php?alabama">Alabama</a>
 

 

MySQL Query sample:

 

$query = "select items from table_name where state = '$_GET[state]';
 

 

Which will then query the database based off the state name and then spit out an array with the data, etc.

 

I'm not too sure what the "safest" way to accomplish this is though since I've been informed that this will lend to possible SQL injection.

 

Any suggestions on how to escape this properly?

[requinix]

First is to make sure you're using the PDO or mysqli extensions. Not the mysql extension (whose functions are all named mysql_*) because it's old and not as useful.

 

Once you've switched, PDO and mysqli both support prepared statements where you can write a query with variables

select items from table_name where state = ? (mysqli)
select items from table_name where state = :state (pdo)

and then bind the state name to that variable. Then you don't have to worry about SQL injection at all: no need to quote stuff, no need to escape stuff.

[ultraloveninja]

Doing a phpinfo(); should let me know if mysqli is supported, right?

[requinix]

Yes, it will list mysql and possibly PDO and/or mysqli. Also note if it mentions "mysqlnd" anywhere.

[ultraloveninja]

Ok, cool.

 

So then this:

 

 

$query = "select items from table_name where state = '$_GET[state]';
 

 

 

Would become this:

 

 

 

$mysqli_query = "select items from table_name where state = ?
 

 

Correct?

[requinix]

Yes, then you do a mysqli::prepare(), mysqli_stmt::bind_param(), and mysqli_stmt::execute(). (Then fetch the results.)

 

Take a look at mysqli in the manual. Besides the documentation there's plenty of examples and user comments.