Php - trying to insert urls into a MySQL table

Question

I am simply trying to insert these objects into a table with php.

$sql = 'INSERT INTO table VALUES( '.$active.' , '.$id.' , '.$time.' , '.$url.' ,"some string" )';

The url in the above code is: http://www.youtube.com/watch?v=sAYc3gGjYW8

When I leave the url column empty it works, when I put an url in it then it doesnt work and I get the following error.

"Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '://www.youtube.com/watch?v=sAYc3gGjYW8 ,"some string" )' at line 1."

QUESTION:

Why does the url not insert just like a normal string?

Is there some sort of function I need to perform on the url_string before it is accepted by MySQL?

PS - the url column is currently VARCHAR(256).

Any help appreciated guys...

Matty · Accepted Answer · 2011-05-30 02:47:49Z

7

You're not escaping your inputs. mysqli_real_escape_string() is your friend.

Remember all input is evil. Validate and sanitize, otherwise you're going to be subject to a whole host of nastiness, from data that's out of bounds (124 char long strings when the field is varchar(10), for example) to opening your code up to SQL injection exploits.

Example:

$safe_url = mysqli_real_escape_string($database_connection_object, $url);

Also, you might want to save yourself some keystrokes, change that string to a double quoted one and interpolate the variables - i.e. "blah blah $some_var foo foo" is the same as 'blah blah ' . $some_var . ' foo foo'

edited May 30, 2011 at 2:47

answered May 30, 2011 at 2:41

Matty

34.9k14 gold badges69 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Julian · Accepted Answer · 2011-05-30 02:59:44Z

3

While I completely agree with the mysql_real_escape_string() comments, it looks like you forgot to wrap the URL in quotes just like any other string should be.

$sql = 'INSERT INTO table VALUES( '.$active.' , '.$id.' , '.$time.' , "'.$url.'" ,"some string" )';

You can tell by looking at the MySQL error:

://www.youtube.com/watch?v=sAYc3gGjYW8 ,"some string" )

There is no quote at the end of the URL :)

answered May 30, 2011 at 2:59

Julian

9,2009 gold badges54 silver badges93 bronze badges

Comments

mjec · Accepted Answer · 2011-05-30 02:49:00Z

You need to escape your input. Have a look at mysql_real_escape_string or mysqli_real_escape_string. You might also want to look at a database abstraction layer like PDO.

Assuming you're using the mysql_* procedural functions, and after you've connected to the database, your script should look like this:

$sql = 'INSERT INTO table VALUES( '.$active.' , '.$id.' , '.$time.' , "'.mysql_real_escape_string($url).'" ,"some string" )';

I would also strongly recommend escaping the other values as well:

$sql = 'INSERT INTO table VALUES( "'.mysql_real_escape_string($active).'" , "'.mysql_real_escape_string($id).'" , "'.mysql_real_escape_string($time).'" , "'.mysql_real_escape_string($url).'" ,"some string" )';

unless they are SQL expressions or you have previously escaped them.

Sotiris · Accepted Answer · 2011-05-30 09:44:45Z

1

Make sure you've escaped all of the inputs into the DB using something like mysql_real_escape_string($string). It'll stop you being open to SQL injection attacks and make sure strings are being read correctly.

edited May 30, 2011 at 9:44

Sotiris

40.2k11 gold badges55 silver badges86 bronze badges

answered May 30, 2011 at 2:48

Crimsonfox

4222 gold badges9 silver badges20 bronze badges

Comments

Robert · Accepted Answer · 2014-05-15 08:55:58Z

-1

$id = $_GET['id'];$name = $_GET['name'];$lat = $_GET['lat'];$long = $_GET['long']; $query = mysql_query("INSERT INTO dbname.tablename (id,name,lat,long) VALUES ('".$id."','".$name."','".$lat."','".$long."')");

edited May 15, 2014 at 8:55

Robert

5,31843 gold badges70 silver badges115 bronze badges

answered May 15, 2014 at 8:38

ridvan

7531 gold badge12 silver badges35 bronze badges

1 Comment

ridvan Over a year ago

it works just add ('id','name','lat','long') make quotation mark like this;press same time altgr and comma later press space button

Collectives™ on Stack Overflow

Php - trying to insert urls into a MySQL table

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related