Proper mySQL command for adding URLs

Question

I'm having a problem when trying to add a URL to a mySQL database.

The string is a URL:

http://pbs.twimg.com/profile_images/1708867059/405000_10150426314376065_707061064_8645107_703731598_n_normal.jpg

The error I get is:

Error description: 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 '://pbs.twimg.com/profile_images/1708867059/405000_10150426314376065_707061064_86' at line 1

It seems as though it won't allow me to add a URL, I presume there is something wrong with some of the characters but I don't know what?

My SQL is:

INSERT INTO accounts (name,consumerkey,consumersecret,pic_url) VALUES ($twitterID,$consumerkey,$consumersecret,$picture_url)"

You’re probably missing the quotes surrounding the string literal. — Gumbo, Nov 12 '14 at 17:13
Showing your SQL would take the whole guesswork out of things. — Funk Forty Niner, Nov 12 '14 at 17:14
You are undoubtedly vulnerabel to [sql injection attacks](http://bobby-tables.com). — Marc B, Nov 12 '14 at 17:15
Great thanks for the edit. You need to quote your *string* values. — Funk Forty Niner, Nov 12 '14 at 17:15
Change $picture_url to '$picture_url'.. But still: use prepared queries to prevent sql injection. — Jeroen de Jong, Nov 12 '14 at 17:16
use `'` or `"` <-------- these little two thingys are very important.... — itachi, Nov 12 '14 at 17:17

Kzqai · Answer 1 · 2014-11-12T17:32:29.997

0

You cannot truly solve this kind of problem by adding a few characters (like ' or ") to your bespoke sql string!

Instead, get to know the real way to write sql in php (it's like a very badly kept secret), which is to use PDO statements. This will allow you to use placehoders like (:twitterID, :consumerKey, :consumerSecret, :pictureUrl) which will accept complex variables such as urls and any of the crap users send in much more gracefully.

In the long run, this will save you a lot of trouble and time.

edited Nov 12 '14 at 17:32

answered Nov 12 '14 at 17:17

Kzqai

22,588
25
105
137

Can you offer any help when writing prepared statements for a simple select query like this? I've been trying all morning but all the examples have loads of extra stuff in and I don't know what I need to omit. – Francesca Nov 14 '14 at 10:03
Well, you can try using these database/pdo wrappers: http://pastebin.com/BQAfiFmT Obviously they'll need some modification for your particular case, but being able to go `query_item('select count(*) from users where firstname = :name', array(':name'=>$bob));` is quite useful. – Kzqai Nov 14 '14 at 16:28

score 0 · Answer 2 · edited May 23 '17 at 12:12

You need to quote string values and any other character that SQL will complain about, in this case it's the colon; see further down below.

($twitterID,$consumerkey,$consumersecret,'$picture_url')

or

('".$twitterID."','".$consumerkey."','".$consumersecret."','".$picture_url."')

if you wish to quote all the values.

Sidenote: You can remove the quotes around the variables that are integers.

I.e.:

This based on, and without seeing how the rest of your code looks like:

$picture_url = "http://pbs.twimg.com/profile_images/1708867059/405000_10150426314376065_707061064_8645107_703731598_n_normal.jpg";

The error states that it is near : - near being just that, the colon.

...right syntax to use near '://pbs.twimg.com
                             ^ right there

You can also use:

VALUES ($twitterID, $consumerkey, $consumersecret, '" .$dbcon->real_escape_string($picture_url) . "')";

$dbcon is an example of a DB connection variable and based on mysqli_ syntax.

Something you haven't stated as to which MySQL API you are using.

Plus, your present code is open to SQL injection.
Use prepared statements, or PDO with prepared statements.

Proper mySQL command for adding URLs

2 Answers2