| Author |
Topic |
|
sockdaddy
Starting Member
3 Posts |
 Posted - 2002-10-29 : 03:11:03
|
| Is anyone aware of a method or service to perform Credit Card Validation, and thereby Billing, directly from SQL Server 2000?I'm developing my first e-commerce website, and the products are actually subscriptions. We need the ability to process recurring billing, that is: Once every month, on the 1st, bill every member his subscription fee, and keep his account active for the coming month. I'd like the ability to do this directly from SQL Server 2000 for two reasons. 1) I'd like to schedule this process as a job, and 2) I'd like to avoid sending the credit card numbers back and forth between the DB and my ASP scripts.Also, I'm not looking forward to building the billing script in ASP, and coding for declined cards - it would be nice to handle everything within SQL (using Transactions, I hope).Sorry if this is a novice question, but I'm really stumped. I've yet to choose a processing service, so I'm open to suggestions. sockdaddywww.sockdaddy.com |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-29 : 03:41:55
|
| How are you going to do credit card validation?You can get the check digit algorithms for all the institutions to validate those (not a small job in itself) but you will also need the black list which is not freely distributed and to keep your valid card list up to date.You would normally send a request to a credit card authorisation company to do the check (and yes you would have to pay for that).You could call a com component from sql to do this or put the requests in a table and have an app servicing it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sockdaddy
Starting Member
3 Posts |
Posted - 2002-10-29 : 14:56:15
|
| Sorry, guess I wasn't specific enough. I shouldn't have said validation - I'll be running numbers through the Lunh algorithm before they get sent to the database.What I really meant is that I was to be able to do all the billing a processing from within SQL Server. I want to run a job once a month that charges the credit card numbers of all subscribed members, and records the transaction details in the database - keeping all members whose cards were successfully charged active for the coming month, and sending a "card not accepted" or "funds not available" type message to the rest.I do realize that I'm going to need to go through a 3rd party to do the billing, Versign, etc...So, based on your reply, do you have any ideas or examples/links of how I can achieve this? TIA.sockdaddywww.sockdaddy.com |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-29 : 15:17:00
|
| Well, if you have an ASP page with the appropriate code to perform the billing transaction, you can move that code into an ActiveX job step and run it from SQL Server more or less the same way it would run on the IIS server. You'd need to remove or rewrite any of the object calls that are ASP specific, anything with a Server or Response or Request object needs to be changed, like:Set obj=Server.CreateObject("ADODB.Connection")...change to...Set obj=CreateObject("ADODB.Connection")Otherwise, any other COM object calls would work exactly the same way. You can handle the process as a transaction across the entire job, or within each job step. Any database updates would be handled through ADO or whatever method you use. You could also use the sp_OA system procedures to manage COM objects (see Books Online under sp_OACreate) but you'd have to translate any VB code that you use to manage the object. It may not be eaiser or better to do it entirely in a T-SQL stored procedure, you'd really have to try both methods (T-SQL and ActiveX job step) and see which works best for you.Take a look at some ASP sites for examples, I know I've seen some credit card validation and processing stuff on at least one of these:www.4guysfromrolla.comwww.15seconds.comwww.asp101.comwww.aspalliance.comwww.asptoday.com |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-10-29 : 15:37:25
|
| One piece of advice I can give you on this is DO NOT stored the CC numbers un-encrypted in the database. You MUST encrypt the numbers in the database. The reason is if anyone ever hacks your system, they will have everything they need to use those cards to buy stuff with.Storing CC numbers in a database is highly dangerous.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
sockdaddy
Starting Member
3 Posts |
Posted - 2002-10-31 : 01:39:18
|
| any tips or suggestions on how to encrypt the cc numbers? details would be great, or links.sockdaddywww.sockdaddy.com |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-31 : 08:22:32
|
| Try this site:http://www.sqlteam.com/redir.asp?ItemID=6391SQL Server has some internal, undocumented encryption routines, but none for decrypting, and the encryption is pretty weak and has been broken by several security organizations. The XP_CRYPTPO tool is far more powerful. |
|
|
|
nizmaylo
Constraint Violating Yak Guru
258 Posts |
Posted - 2003-02-11 : 19:58:36
|
| Rob,if someone hacks the database, what's going to stop them from running the xp_crypto to decrypt those encrypted values?helena |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-11 : 20:02:33
|
| You need the key or hash value to decrypt them properly, and some of the hashing functions are one-way; you can't decrypt them at all, you can only compare two encrypted values. |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-02-12 : 14:12:25
|
I'm in the process of designing a similar system. Based on my research, here's my advice- Don't do the processing from SQL server. It's really not meant to be a traditional programming environment, and you'll find that everything from error handling to documentation is harder
- Do store the CC numbers in encrypted form, but use some form of two-way crypto. If you have to resubmit a charge, or if you're going to do recurring billing, having one-way crypted numbers will do you no good
- Use a standalong application (VB, VC, C#, whatever) do do the actual CC processing. That way the external app can have the decrypt keys, and a SQL compromise won't give the CC numbers out (if they get both your SQL server and the source code to your processing app, well, you did something wrong)
For low volume application, you could probably hack something together, but my advice would be: don't. Do it right and in a manner that will be scalable, both in terms of performance and security.Cheers-b |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-02-12 : 17:00:06
|
| Recurring billing, without having to store the credit card numbers in the database. This system ONLY works for a fixed amount each month.http://www.verisign.com/products/payflow/rcb/index.htmlMichael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
Credit Card Validation ???