CODEWITHSUNDEEP
phpmysqlurlencodeurldecode
JYeh
JYeh

Reputation: 4333

Using urldecode() results in MySql error

I need to pass special symbols through the URL for my MySQL query. For example, I need to have a URL that is something like: 

www.example.com/index.php?q=AND name LIKE '%hi%'

When I tried it at first, I got a 406 error. I looked it up and apparently I have to use urlencode() and urldecode(). I put those in, and the 406 error went away, but then I got a MySQL error: 

mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Usually when I get these it means the query isn't written properly. So I echoed MySQL query, and everything looked fine. I even removed the urldecode() and hard-coded into a variable what I wanted to be passed to the page, and the MySQL error went away. However, both queries from using urldecode() and not using that are EXACTLY the same, so I'm kind of confused.

I went onto the php.net documentation page for urldecode(), and there was a warning that said something like using _GET and urldecode() together can result in unexpected things, and that _GET already functions as a decoder (or at least that's how I interpreted the wording), so I removed urldecode() but still left in the _GET, and that resulted in the text not being decoded, so I guess I didn't interpret the documentation correctly.

Is urldecode() not compatible with MySQL queries? I'm fairly certain it's an issue with the encode/decode, since I already tested my code with hard-coded info that bypassed the encode/decode, and it worked fine. Maybe the urldecode() is somehow turning the characters into special ones that look the same but are internally different so MySQL can't read them?

Upvotes: 1

Views: 7263

Answers (3)

Rich Bradshaw
Rich Bradshaw

Reputation: 72965

Don't do this. It's wrong. Anyone can of course just end the query using a ; and start a new one, deleting everything or reading out users and passwords.

One easy and much better way to do this is to use www.example.com/index.php?q=hi as your URL, then on the server (let's assume PHP)

$queryString = mysql_real_escape_string($_GET['q']);

$query = "AND name LIKE '%$queryString%'";

// Then replace $query for whatever you were using $_GET['q'] for before.
// Feel free to rename my variables what you like!

This way, the user can't mess things up, and the URL looks cleaner. the mysql_real_escape_string function makes the string safe to use in the query by escaping things. Read http://php.net/manual/en/function.mysql-real-escape-string.php for more on that.

If you still aren't convinced this is useful, consider what happens if someone publishes a link that will drop your tables, and then Google crawls it once a week. Your data will be removed any time that Google happens to swing by.

Once you are happy with this technique, read up on mod_rewrite (if using Apache) to clean the URL up even more, mysqli and how it is an improved version of the mysql functions, and finally PHP Data Objects (PDO) which helps to clean the PHP up even more.

Upvotes: 75

rjmunro
rjmunro

Reputation: 28056

At some point after calling mysql_query() and before calling mysql_fetch_array() you should check that the query didn't return false, and print mysql_error() if it did. e.g.:

$result = mysql_query($query);
if (!$result) {
   die(htmlenitites(mysql_error())." when running <pre>".htmlenitites($query)."</pre>");
}

But:

  1. You shouldn't be using php's mysql functions, you should use PDO, and you should use prepared / parameterized queries.
  2. You shuoldn't be letting SQL be passed from the client unless you really trust anything the client might type - e.g. this is for a back-end admin interface, and even then, it's probably bad practice unless you really need it (like you are writing phpMySQLAdmin)

Upvotes: 0

Mason Wheeler
Mason Wheeler

Reputation: 84550

Rich has the right general idea, but even better than quoting is to use database parameters. It essentially places a "variable token" into your SQL query, and then passes the variable separately. The query ends up looking something like this:

SELECT ID, VALUE1, VALUE2
FROM MY_TABLE
WHERE VALUE3 = :param

And then in your code, you add a value to substitute in for :param, and then send that to the database alongside your SQL. (On some DB libraries, you'd use a ? instead of a :parametername.) It works better than quoting for three reasons:

First, you can keep the query string constant instead of having to rebuild it every time, which improves performance in your server.

Second, if you send the same constant query to the database multiple times with multiple different parameters, the database engine can cache the query plan, which improves performance on the DB.

Third, when you get used to writing your queries in parameterized style, it becomes natural, especially if you use a query function that takes a parameter list as an argument, so it's hard to get wrong. By contrast, it's easy to forget to quote something, and then you've accidentally opened a security hole in your program.

Exactly how parameterization works depends on the database and the DB access library you're using, but every SQL database supports them. You should look at how it's done for the system you're using. It really is the best way to work with SQL, especially when you have input coming from untrusted sources such as the Internet.

Upvotes: 8

Related Questions