SQL Errors With 's

I have a joke site - www.captainfunny.com

However, I'm finding that whenever I put a ' in a joke I submit, it causes an SQL error. I figure this is because it's ending my code or something.

Anyways, anyone know how to fix something like this?

 

Your site is completely open for XSS scripting attacks without anyone even trying.

EVERY, and I MEAN EVERY SINGLE VARIABLE that exists within a query needs to be escaped.

mysql_real_escape_string();

$query = mysql_query("SELECT * FROM table WHERE column = '".mysql_real_escape_string("$_GET['variable']")."'");

Doing this will also fix your single quote problem.

 

Thanks! Where do I put this code?

 

I put it in my conn.php, and got:
Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/.franchesca/tccpg288/captainfunny.com/conn.php on line 7

 

When you use a MYSQL query, you should mysql_real_escape_string() your variables.

For example.

MYSQL_QUERY("SELECT * FROM jokes WHERE id='".$_GET['id']."'");

PHP:

This takes the ID variable and selects a joke, however you should use the mysql_real_escape_string() function around it.

MYSQL_QUERY("SELECT * FROM jokes WHERE id='".mysql_real_escape_string($_GET['id'])."'");

PHP:

Like that, wrap it directly around the variable like any other.

And here is abit of a more easier breakdown for you to understand it.

$_GET['id'])=mysql_real_escape_string($_GET['id']);
MYSQL_QUERY("SELECT * FROM jokes WHERE id='".$_GET['id']."'");

PHP:

Paste your code if you have further problems

 

Just for the sake of good order.

The code is vulnerable to SQL injections, not XSS attacks.

XSS attacks allow users to insert custom HTML code which can endanger the client but not the server.

 

Yeah, my fault I was in a hurry when I typed that and put the wrong thing. Sorry!

 

Would anyone be willing to have a look at my code and fix all these SQL bugs? I'm not that great at PHP.

 

err... i must be blind - cause i cannot see where you put your code for viewing or a link to the code.

 

I tried to use mysql_real_escape_string on some $_GET variables on my site, I kept getting an access denied error, I looked it up and said that you need to have a link identifier, the PHP script my site uses looks like this:

mysql_connect('localhost', $username, $password);

PHP:

Since I needed a variable for the link identifier I added another line below that one:

$EscapeLink = mysql_connect('localhost', $username, $password);

PHP:

Is it ok that I have those 2 instances of mysql_connect like that or will that overload the DB with too many connections?

My code includes mysql_close() not far after those 2 lines..in all it looks like this:

mysql_connect('localhost', $username, $password);
$EscapeLink = mysql_connect('localhost', $username, $password);
@mysql_select_db($database) or die("Unable to select database");
$result = mysql_query($query);
$num = mysql_num_rows($result);
mysql_close();

Few lines of code here...

$SubCategoryGet=mysql_real_escape_string($SubCategoryGet, $EscapeLink);

PHP:

 

Use parameterised queries, so you don't have to worry about escaping anything.

mysqli (PHP 4/5), PEAR::MDB2 (PHP 4), and PDO (PHP 5) all support these.

 

I dont know what you mean by parameterised queries...

Is that when you do the thing that looks like [0-0a-zA-Z] or something similar?

 

No, those are regular expressions.

Parameterised queries use placeholders in the query, rather than concatenating or embedding values directly into it. The values are then passed separately to the query - hence 'parameterised'.

For example:

$stmt = $dbh->prepare('insert into employees (name, age) values (?, ?)');
$stmt->execute("Patrick O'Reilly", 52);

PHP:

That also happens to be a prepared statement, in that the query is only parsed by the DBMS once and not each time it is executed.

As you can see, there is no need to escape the apostrophe in O'Reilly, as it is not inserted into the query at all.