CODEWITHSUNDEEP
phpmysqlinsertspecial-characters
viktorg
viktorg

Reputation: 115

How do I INSERT the character "&" into a MySQL database?

I think I have seen this question before but I don't think it's answered good enough yet because I can't get it to work.

The case:
I want to insert an URL into my MySQL database like so:

$url = $_POST["url"]; //$_POST["url"] = "http://example.com/?foo=1&bar=2& ...";

$sql = mysql_query("INSERT INTO table(url) values('$url')") or die ("Error: " . mysql_error());

Now, the URL is inserted into the database properly but when I look at it, it looks like this:

http://example.com/?foo=1

It's like the URL is cut right at the "&" character. I have tried: mysql_real_escape_string, htmlspecialchars, escaping by doing "\" etc. Nothing seems to work.

I have read that you might be able to do it with "SQL Plus" or something like that.

Thanks in advance.

Regards, VG

Upvotes: 0

Views: 3011

Answers (5)

Jacques GOUDE
Jacques GOUDE

Reputation: 51

Right !! The problem here is nothing to do with the database query has DaveRandom said. Just use the javascript function "encodeURIComponent()".

Upvotes: 0

DaveRandom
DaveRandom

Reputation: 88647

Chances are the problem here is nothing to do with the database query, and more to do with how the url is passed to the page. I suspect you'll find that the URL used to load the page is something like:

http://mydomain.com/?url=http://example.com/?foo=1&bar=2

This will result in a $_GET that looks like this:

array (
  'url' => 'http://example.com/?foo=1',
  'bar' => '2'
)

What you need is to call page with a URL that looks more like this:

http://mydomain.com/?url=http://example.com/?foo=1%26bar=2

Note that the & has been encoded to %26. Now $_GET will look like this:

array (
  'url' => 'http://example.com/?foo=1&bar=2'
)

...and the query will work as expected.

EDIT I've just noticed you're using $_POST, but the same rules apply to the body of the request and I still think this is your problem. If you are, as I suspect, using Javascript/AJAX to call the page, you need to pass the URL string through encodeURIComponent().

Upvotes: 5

tetris
tetris

Reputation: 4362

maybe escape the url with urlencode then you can decode it if you want to pull it out of the db

Upvotes: -1

Max
Max

Reputation: 1040

Depending on what you want to do with the stored value, you also urlencode() the string: http://php.net/manual/de/function.urlencode.php

Cheers, Max

P.S.: SQL*Plus is for Oracle Databases.

Upvotes: -1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

It is likely the querystring is not being passed. It looks like you are receiving it from a FORM post. Remember that form posts that use a method of GET append a querystring to pass all of the form variables, so any querystring in the action is typically ignored.

So, the first thing to do is echo the URL before you try to INSERT it to make sure you are getting the data you think you are.

If there are variables you need to pass with the URL, use hidden inputs for that, and a method of GET on the form tag, and they will get magically appended as querystring parameters.

Upvotes: 0

Related Questions