SQL query

#1
chili5

Posted 02 April 2009 - 09:22 AM

Writes binary right handed and hex left handed

Members
7,247 posts

Can anybody see why this SQL query isn't working?


$query = "INSERT INTO articles (date,title,article_text,user) VALUES('$dates','$title','$article','$_SESSION[username]')";

I know I'm getting all the values from the form correctly, and got a successful database connection. Although I have no idea why the query isn't working. :confused:

#2
John

Posted 02 April 2009 - 11:53 AM

Writes binary right handed and hex left handed

Moderators
6,321 posts

$_SESSION[username] needs to be $_SESSION['username'] but since it is inside single quotes, it will probably need to be $_SESSION[\'username\'] (i think).

Compiled Thoughts

#3
Orjan

Posted 02 April 2009 - 12:19 PM

Writes binary right handed and hex left handed

Moderators
3,299 posts

Why not do

$query = "INSERT INTO articles (date,title,article_text,user) VALUES('".$dates."','".$title."','".$article."','".$_SESSION['username']."')";

to make sure everything works?

__________________________________________
I study Information Systems at Karlstad University when I'm not on CodeCall

#4
Guest_Jordan_*

Posted 02 April 2009 - 12:59 PM

Guests

Orjans example should work. You could also just wrap {} around it:


$query = "INSERT INTO articles (date,title,article_text,user) VALUES('$dates','$title','$article','{$_SESSION[username]}')";

#5
xsonline

Posted 02 April 2009 - 01:54 PM

Learning Programmer

Members
48 posts

It could also be that you have used a ' in your strings. Also this query has a lot of risks for sql injection.

$query = "INSERT INTO articles (date,title,article_text,user) VALUES('".mysql_real_escape_string($dates)."','".mysql_real_escape_string($title)."','".mysql_real_escape_string($article)."','".mysql_real_escape_string($_SESSION['username'])."')";

This might seem quite lame, but it's way more secure, and more likely to work in any situation. Also a slimple addslashes might work in stead of mysql_real_escape_string, but then you'll need stripslashes for every output also.

I hope this helps

#6
chili5

Posted 02 April 2009 - 02:24 PM

Writes binary right handed and hex left handed

Members
7,247 posts

Um no I tried Jordan's code and your code xs and nothing. :-s

Does it help if I post my entire script?

<?php
session_start();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<title>Programming Journal - Add an Entry</title>
		<link rel="stylesheet" href="style.css" type="text/css" />
	</head>
	<body>
		<div id="container">
			<div id="header">
				<h1>Programming Journal</h1>
				<p>
				<?php
					if (isset($_SESSION['perm']) && $_SESSION['perm'] == "admin") {
						echo "<a href=\"create.php\">Write a Journal</a><br />";
					}
				?>
				</p>
			</div>
			<div id="nav">
				
				<?php
				require "includes/nav.php";
				?>
			</div>
			<div id="content">
				<p>
				<?php
				if($_SESSION['perm'] == "user" || !isset($_SESSION['perm'])) {
					echo "You do not have permissions to write!";
					die("");
				}
				
				if (!isset($_POST['add'])) {
				?>
				</p>
				<form method="post" action="create.php">
				<table border="0">
					<tr>
						<td>Title</td>
						<td><input type="text" name="title" /></td>
					</tr>
					<tr>
						<td>Date</td>
						<td><input type="text" value="<?php echo date("Y-m-d");?>" name="dates" /></td>
							<tr>
								<td valign="top">
									Message:
								</td>
								<td>
									<textarea rows="20" cols="80" name="article"></textarea>
								</td>
							</tr>
							<tr>
								<td>Allowed formatting:</td>
								<td>HTML formatting is allowed!</td>
							</tr>
							<tr>
								<td colspan="2">
									<input type="submit" name="add" value="Write" />
								</td>
							</tr>
					</tr>
				</table>
				<?php
				} else {
					mysql_connect("localhost","root","T1fjks");
					mysql_select_db("journal");
					$title = strip_tags($_POST['title']);
					
					$article = $_POST['article'];
					$dates = $_POST['dates'];
					$query = "INSERT INTO articles (date,title,article_text,user) VALUES('$dates','$title','$article','{$_SESSION[username]}')";  
					
					
					mysql_query($query);
					echo "Article added!<br />";
				}
				?>
				
			</div>
			
		</div>
	</body>
</html>

#7
John

Posted 02 April 2009 - 02:26 PM

Writes binary right handed and hex left handed

Moderators
6,321 posts

Jordan said:

Orjans example should work. You could also just wrap {} around it:


$query = "INSERT INTO articles (date,title,article_text,user) VALUES('$dates','$title','$article','{$_SESSION[username]}')";

yes, but username is still being referred to as a constant. Sometimes PHP will interpret undefined constants as string literals, but relying on that functionality is a bad practice.

Compiled Thoughts

#8
Guest_Jordan_*

Posted 02 April 2009 - 02:39 PM

Guests

What error do you get? Have you echoed the query and ran it directly in mysql?

#9
Orjan

Posted 02 April 2009 - 02:52 PM

Writes binary right handed and hex left handed

Moderators
3,299 posts

my standard thing:

do

$sql = "INSERT INTO articles (date,title,article_text,user) VALUES('$dates','$title','$article','{$_SESSION[username]}')";

$query = @mysql_query($sql) or die ("SQL: ".$sql."\nError: ".mysql_error());

then you get everything black on white, how the variables are interpreted and how the sql is acting...

__________________________________________
I study Information Systems at Karlstad University when I'm not on CodeCall

#10
John

Posted 02 April 2009 - 02:58 PM

Writes binary right handed and hex left handed

Moderators
6,321 posts

From the looks of it, other than less than ideal coding practice, there is nothing wrong with your code. My best guess is you have a spelling error in your query.

Compiled Thoughts

#11
chili5

Posted 03 April 2009 - 01:18 AM

Writes binary right handed and hex left handed

Members
7,247 posts

Ah orjan you are brilliant!

Quote

SQL: INSERT INTO articles (date,title,article_text,user) VALUES('2009-04-03','test','test','james')
Error: Duplicate entry '0' for key 'PRIMARY'

Although I'm sure I set auto_increment. :confused:

What is less than ideal though?

#12
xsonline

Posted 03 April 2009 - 02:29 AM

Learning Programmer

Members
48 posts

chili5 said:

Ah orjan you are brilliant!

Although I'm sure I set auto_increment. :confused:

What is less than ideal though?

Well, the fact you use just the strings to insert into the query. I'll give you an example of what might go wrong:
Someone uses a ' in their article

$article="don't use this.";

$title="test";

$dates="2009-04-03";


$query = "INSERT INTO articles (date,title,article_text,user) VALUES('$dates','$title','$article','$_SESSION[username]')";

the query now will be:

Quote

INSERT INTO articles (date,title,article_text,user) VALUES('2009-04-03','test','don't use this.','James')

when you try to run this query, it will result in an error because of the ' .

A solution to this is "mysql_real_escape_string". This will add a \ to every special character like this:
'=>\'
"=>\"

=>\n

This way you make sure there cannot be errors by using special characters,and with selects, updates and deletes, you secure your query for sql injection.

sql injection occures like this:

$sql="SELECT * FROM table WHERE name='".$name."'";

now if someone does the folowing:


$name="test' OR ''='"

$sql="SELECT * FROM table WHERE name='".$name."'";

the query will be:
$sql="SELECT * FROM table WHERE name='test' OR ''=''";

wich will result in every row in your table. now this will only show every row in your table, but imagine an UPDATE or a DELETE FROM.

I hope this helps