Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

SQL syntax error -> email address

Quote Reply
SQL syntax error -> email address
I am attempting to write a script that will update records in the Users table. I am using the following syntax in the modify script:

Code:

my $email = $in->param('Email');
my $email_perm = $in->param('Email_Permission');
my $password = $in->param('Password');
my $username = $in->param('Username');
my $user_perm = $in->param('Username_Permission');
# Get the link.
$db = new Links::DBSQL "$LINKS{admin_root_path}/defs/Users.def";
$rec = $db->get_user_record ($username, 'HASH');
my $title_linked = &build_linked_cgi_title ("Modify Account/Error: Unable to Process Form");
$rec or &site_html_error ( { error => "Invalid Account!", title_linked => $title_linked}, $dynamic),return;

# Convert to hash.
foreach $key (keys %{$rec}) {
$original->{$key} = $rec->{$key};
}
$status = $rec->{'Status'} = $original->{'Status'};
$userid = $rec->{'UserID'} = $original->{'UserID'};
$validation = $rec->{'Validation'} = $original->{'Validation'};

# Update User Table.
$rec = $db->prepare ("SELECT 1 FROM Users WHERE UserID = $userid");
$rec->execute();
$db->do ("UPDATE Users SET UserID=$userid, Username=$username, Password=$password, Email=$email, Email_Permission=$email_perm, Username_Permission=$user_perm, Validation=$validation, Status=$status WHERE UserID = $userid");


Mind you that this is not the complete processing sub.

When I go through the forms and submit the final confirmation form, I get the following error message:

Code:

DBSQL (27731): Fatal Error: Unable to execute query: UPDATE Users SET UserID=value, Username=value, Password=value, Email=value@anthrotech.com, Email_Permission=value, Username_Permission=value, Validation=value, Status=value WHERE UserID = value. Reason: You have an error in your SQL syntax near '@anthrotech.com, Email_Permission=value, Username_Permission=value, Validation=value' at line 1 at /somepath/modacct.cgi line 239


I have bolded line 239 in the first set of codes. I also have replaced the actual values of the fields with value for security purposes. The edited values are correct. That does not seem to be a problem.

I believe that the @ in the email address is being interpreted as an array, which is causing the script to choke.

Any thoughts about how to fix these codes would be greatly appreciated.

Thanks in advance.

BTW: I have read through many chapters of the MySQL manual regarding the UPDATE operation with no success.

Regards,

Eliot Lee

Quote Reply
Re: SQL syntax error -> email address In reply to
You need to escape the values... I think you can pass them to the ->escaped routine, to properly format them, _BUT_ a common mistake is to forget that the SQL statement is just that -- a STATEMENT.

you can't really do something like " Key='$value'", because you can start getting into problems.

You need to create a $value that is really "'".$value."'" -- in otherwords, unless they are numeric, values need to be enclosed in single quotes, and it doesn't hurt for numerics either.

Also, with the email address, you probably do need to escape it, "\@"

I'm trying to remember where I did that...and had to work with this sort of data. Alex had a few suggestions....

Maybe do a search in this forum for "escape" and if that is too broad, try tolimit it with something like "statement" or "SQL" or "value"

I'm pretty sure this was in the past 3 months or less.

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: SQL syntax error -> email address In reply to
Thanks, pugdog! It worked. For some reason, when I tried escaping the values before, I kept getting HASH errors. But I re-worked the values with single quotes and it worked!

Thanks again!

Now...if I can only figure out how to get the build process to complete, I will be able to release my upgraded site...yes, I have read the FAQs and Threads in the Forums about the build process....I may just wait til the BETA release and in the interim use page.cgi.

Regards,

Eliot Lee

Quote Reply
Re: SQL syntax error -> email address In reply to
What's wrong with the build process?

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: SQL syntax error -> email address In reply to
The process is killed after building about 1,000 links (out of 2,235) and only 100 categories (out of 396).

Regards,

Eliot Lee

Quote Reply
Re: SQL syntax error -> email address In reply to
You are not on a shared server, and you haven't installed any "monitor" daemon that kills a process after x-minutes, x-minutes of CPU, or something similar?

Usually, in fact in all the cases that I remember coming up, whenever a process is terminated at relatively the same place (especially if it's unit-time) it's turned out to be the above.

If you are doing it via HTTP:, your server may be timing out the nph- connection, and you need to run the script via telnet. But, running via telnet will not get around the daemon killer mentioned above.

If it's _not_ one of these problems, I really want to know to add to the list. Checking my notes, the only other thing that killed a process was running out of disc space, but that caused other problems -- and you wouldn't have been able to keep developing (unless your web pages are built on another partition/device? from where the scripts are run)


As noted -- the new links has 3 build options in the admin (none connected yet) for build changed, build staggered and build all.




http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: SQL syntax error -> email address In reply to
I am on a semi-dedicated server with limited accounts. And the sys admin has set a process kill time of 5 minutes.

I am executing the script via telnet and also tried setting up a Crontab and it timed out as well.

And I have already read through ALL the Build Problem Threads in the Links SQL forums.

The crazy thing is that with Links 2.0, I was using four database files to build my directory pages and it ran under a minute. I know that with Links SQL it is way more complex...(since it uses 6 or 7 tables to build the directory)but it still frustrating that I cannot build my directory.

Until I upgrade to the new version, I will stick with using page.cgi unless I can identify my current build problem.

Thanks for the input.

Regards,

Eliot Lee

Quote Reply
Re: SQL syntax error -> email address In reply to
Want me to take a look at it?

Sometimes a second pair of eyeballs will yeild the answer. I would still bet it's the process kill time of 5 minutes that's doing it.

Have you watched the processes, and done a top to see what's happening as they run? If you find they are being killed at the same time point, it's the process daemon.

Have you looked at any error messages? Is the program losing contact with the MySQL database for some reason? (process time-out?)

Once the program starts, there really is no reason for it to stop, unless:

1) runs out of disk space
2) runs out of pre-determined limits on time, cpu and/or memory usage
3) hit's an error that is reportable in the error.log for http, or the screen for telnet.
4) hit's a 'stop' somewhere in the code, left over from debugging.
5) hit's a 'permissions' problem in creating files or directories (easily fixed by chmod -R 777 top_dir_name), but this is usually reported correctly in #3

Beyond that, the program is in a loop, executing the same code, over and over, so it's not really doing 'new' things. It's eating memory, diskspace, CPU, and dealing with permissions. Most like it's #2, no matter what your ISP is telling you.

I'd be happy to take a look at it, tracked it down to #2 on more servers than any other problem.




http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: SQL syntax error -> email address In reply to
In Reply To:
I would still bet it's the process kill time of 5 minutes that's doing it.
I know that is the problem...but why it is taking so long to build with so few categories and links is beyond me.


In Reply To:
Have you watched the processes, and done a top to see what's happening as they run?
Yes...it is consuming on average about 80% CPU.

In Reply To:
Have you looked at any error messages? Is the program losing contact with the MySQL database for some reason? (process time-out?)
Nope...the connection is maintained through the build process...the problem is that the process is killed after five minutes.

In Reply To:
Beyond that, the program is in a loop, executing the same code, over and over, so it's not really doing 'new' things. It's eating memory, diskspace, CPU, and dealing with permissions. Most like it's #2, no matter what your ISP is telling you.
Right, I know that...however, I have tried asking them to increase the time limit and they have not done anything about it.

I am a bit wary about other people accessing my account and running scripts. If you email me, I will provide you temporary access to my account. Just fill out my contact form at my web site:

http://www.anthrotech.com - click on Contact Us

Regards,

Eliot Lee